Organize table avoiding redundancy

Question

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?

For example:

Id_Line = 1
ID_Stop = 1
Hour = 4:50

Than

Id_Line = 1
ID_Stop = 1
Hour = 5:20

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?

Answer 1

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?

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.)

Answer 2

Here is the design I'd go with

CREATE TABLE Company(
    CompanyID       SMALLINT IDENTITY(1,1),
    CompanyName     NVARCHAR(12),
    Tel             INT,
    PRIMARY KEY(CompanyID)
);

CREATE TABLE Line(
    LineID      INT IDENTITY(1,1),
    Code        CHAR(3),
    CompanyID   SMALLINT,
    Desc        NVARCHAR(MAX),
    PRIMARY KEY(LineID),
);

CREATE TABLE [Stop](
    StopID INT IDENTITY(1,1),
    geoLat NUMERIC(9,6),
    geoLong NUMERIC(9,6),
    PRIMARY KEY(StopID)
);

CREATE TABLE Make(
    MakeID INT IDENTITY(1,1),
    StopID INT,
    LineID INT,
    [Hour] SMALLINT,
    PRIMARY KEY(MakeID),
);

Changes I made and why:

1. Switched to using identity columns as an INT is smaller than a VARCHAR(3); this pays over again and again on indexes.
2. Switched VARCHARs to NVARCHARs. International potential now added.
3. Desc is now a NVARCHAR(MAX) as TEXT is a deprecated datatype and shouldn't be used. Do you really need that big of a field for a description? If not, downsize it.
4. Primary keys updated to be the indentity columns
5. Hour change from a TIME format to SMALLINT because it is a smaller datatype and I doubt you need to estimate beyond the minute of when a bus will arrive. (1720=5:20PM)
6. Your coordinates were dropped to a numeric(9,6). This should give the the precision you need with the smallest datatype possible.

As far as redundant data, don't fall for the hype. There is a time and place for normalization and it is not EVERY TIME. Adding another table to remove the redundancy of the arrival time would actually cause your database to be bigger, not smaller.