组织表避免冗余 [英] Organize table avoiding redundancy
问题描述
我正在尝试创建一个数据库来管理自动总线数据
I'm trying to create a database to manage autobus data
CREATE TABLE Company(
Company_Name VARCHAR(12),
Tel INT,
PRIMARY KEY(Company_Name)
);
CREATE TABLE Line(
ID_Line VARCHAR(3),
NCompany_Name VARCHAR(12),
Desc TEXT,
PRIMARY KEY(ID_Line, Company_Name),
FOREIGN KEY (Company_Name) REFERENCES Company(Company_Name)
);
CREATE TABLE Stop(
ID_Stop VARCHAR(3),
geoLat FLOAT(10,6),
geoLong FLOAT(10,6),
PRIMARY KEY(ID_Stop)
);
CREATE TABLE Make(
ID_Stop VARCHAR(3),
ID_Line VARCHAR(3),
Hour TIME,
PRIMARY KEY(ID_Stop,ID_Line),
FOREIGN KEY (ID_Stop) REFERENCES Stop(ID_Stop),
FOREIGN KEY (ID_Line) REFERENCES Line(ID_Line)
);
问题是，一辆公共汽车在不同时间在同一站停了几次，此信息避免冗余？
The problem is that a bus stops several times at the same stop in different hours, how could I store this information avoiding redundancy?
例如：
Id_Line = 1
ID_Stop = 1
Hour = 4:50
比
Id_Line = 1
ID_Stop = 1
Hour = 5:20
但这是不可能的，我想添加另一个字段（autoincrement）称为ID，但我不知道它是最好的解决方案。
but this isn't possible, I thought of adding another field (autoincrement) called ID but I didn't know if it was the best solution. What could you advice?
推荐答案
您对谓词感兴趣[ID_Line]在[Id_Stop]小时]。按照定义的表Make将保存那些成为真实的行。其唯一的候选键（因此主键）是（ID_Stop，ID_Line，小时），因为没有列的其他子集是唯一的。您的图表应包括小时（根据您使用的任何图表约定）。对于（ID_Stop，ID_Line）对（不标识Make的行，但不标识曾经停止在的线路停止对）或（ID_Stop，ID_Line，Hour）三元组的ID没有特别的好处。
You are interested in the predicate "[ID_Line] makes a stop at [Id_Stop] at time [Hour]". Table Make as defined will do to hold the rows that make that true. Its only candidate key (hence primary key) is (ID_Stop,ID_Line,Hour) since no other subset of columns is unique. Your diagram should include Hour (according to whatever diagramming conventions you are using). There is no particular benefit to an ID for either (ID_Stop,ID_Line) pairs (which wouldn't identify rows of Make, but line-stop pairs that are ever stopped at) or (ID_Stop,ID_Line,Hour) triplets.
问题是，一辆公共汽车在同一站点多次在
不同的小时停车，我如何存储这些信息避免
冗余？
The problem is that a bus stops several times at the same stop in different hours, how could I store this information avoiding redundancy?
没有这样的问题。子行可以在表中多次出现，无论是否存在冗余。虽然可以用ids加上另一个表格来替换出现多次的子行，但对于同一个查询结果，我们需要更多的连接。 href =http://stackoverflow.com/a/27809411/3404097>此答案。）
There is no such problem. A subrow can appear more than once in a table whether or not there is "redundancy". (Whatever you think that means. Although one can replace subrows that appear multiple times by ids plus another table, then one needs more joins for the same query result. See this answer.)
这篇关于组织表避免冗余的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
