Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a "Bus stations" database. I want to design the next relationship: "A bus line has many stops (one stop belongs to one line) and one stop has many arrival times (one arrival time belongs to one stop)".

So my design is like this:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times

Lines(id, name, ...)
Stops(id, line_id, name, ...)
Times(id, stop_id, ...)

But when I was testing it, I realized that I needed a FOREIGN KEY line_id in Times table (because I could not distinguish between times of different bus lines). This transforms the diagram:

Lines (1,1)<->(1,N) Stops (1,1)<->(1,N) Times
  |                                       |
  |                                       | 
   ---------------------------------------
  (1,1)                                  (1,N)

I'm a little bit confused because I think this design has redundancy: the relationship between Lines and Times could not be reached by the intermediate relationships?

share|improve this question
    
How are you trying to query the Times table? –  Rachcha Sep 2 '13 at 11:23
    
You don't need the extra relationship. You can easily distinguish between times of different bus lines, by joining to the Stops table. –  ypercube Sep 2 '13 at 11:27
    
@Rachcha For example: if I want to select all the arrival times from stop 1 and line 1: select * from times where stop_id = 1 and line_id = 1;" –  enrmarc Sep 2 '13 at 11:31
    
@ypercube but I think that would obfuscate the query. I will have to to something like this select * from times t, stops s, lines l, where l.id = s.line_id and s.id = t.stop_id and l.id = 1 and s.id = 1; –  enrmarc Sep 2 '13 at 11:38

2 Answers 2

up vote 2 down vote accepted

You should not need the extra FK in Times as that information can be derived from the existing relationships, but you will need to JOIN in the other tables to get at the extra property. Adding the extra key like that is sometimes a necessary optimisation but it does break "normal form" as you are duplicating data (meaning that either your business layer becomes responsible for maintaining the referential integrity of that duplicate, or you need to do the same in the database using triggers and other "powerful but be very careful with them" features of your chose database).

Your select * from times where stop_id = 1 and line_id = 1 should be something like:

SELECT times.id, times.stopid, ...
FROM   times
JOIN   stops ON times.stop_id=stops.id
WHERE  times.stop_id=1 AND stops.line_id=1

To simplify queries you can create views that abstract out the underlying structure a little, meaning you can keep the data in best form while dealing with it as it it did have the extra columns with duplicated information.

I'm not sure I'd model a timetable store that way though. I would assume that stops are separate entities with a many-to-many link between lines (unless you are counting a stop at which the no. 4, 5, and 45x lines stop at as three separate stops even though they are physically the same location). Also I'd probably want to group the lines together so that all the times for the "no 4" service are identifiable as a related collection but you can distinguish between the arrival times of each (so you can ask "what is the arrival time at X for the service that leaves Y at HH:MM" and so forth. Of course I could just be misreading your intended model! I'm thinking something like:

                                       TimedStop
                   TimedRoute          =============
Line               =============       ts_id (PK)         Stop
============       route_id (PK) ===>  route_id (FK)      =============
line_id (PK) ===>  line_id (FK)        stop_id (FK)  <=== stop_id (PK)
line_name                              arrival_time       stop_name
                                       depart_time        stop_location

Here TimedStop becomes a many-to-many relationship store for timed routes and stops. Of course there are probably several perfectly valid ways to model such a system depending on your other constraints, and the above may be rendered an incorrect model when you consider the fuller design spec (without more detail of what you are trying to model and what outputs are desired this is not possible to completely pin down, I made a fair few assumptions, which may or may not be correct, when throwing the above diagram together).

As another aside on a point of style: select * is generally best avoided in permanent code where possible. If you are instead specific about what you want out this because part of your API and other code using this output has more guarantees of what columns will be returned, even if the underlying structures are updated. It can also allow the query planner/runner to apply extra optimisations (potentially avoiding extra heap lookups or being able to replace a table scan with an index scan, and so forth).

share|improve this answer
    
Thanks. I'll take your design into consideration. –  enrmarc Sep 2 '13 at 12:28
1  
@David You don't need triggers for integrity, if you add line_id in the Times table. Only the FK (from Times to Stops) has to be adjusted to include that column. –  ypercube Sep 2 '13 at 12:30
    
Yes that would work though only if you are assuming each stop is only used by one line or that if more than one line uses the same physical stop this is modelled as distinct stops in the data (despite being the same location). The former could be quite a limiting assumption, and the latter feels like a clean way to model the situation to me so it didn't cross my mind. –  David Spillett Sep 2 '13 at 13:55

As per the comment you have provided, there is no need to add a line_id column in times table.

You can query like this:

SELECT *
  FROM times t JOIN stops s ON s.id = t.stop_id
  JOIN lines l ON s.line_id = l.id
 WHERE l.name = 'XXX';

This way, you will be able to query all the stop times for all the stops of a particular line by its name (provided l.name is unique). To filter your search add more conditions in your WHERE clause.

As a suggestion for designing, I recommend you name your tables as singulars - line instead of lines, etc. Apart from this, your schema looks properly normalized and there is no need to add further foreign keys for adding anymore relations between these tables. You will need to add foreign keys if, at all, you have to establish relationships with tables other than these.

share|improve this answer
    
Thanks. Now my question is: Is ok to include line_id in table Times just for the sake of clear SQL queries? (or performance maybe) –  enrmarc Sep 2 '13 at 12:03
    
No. This violates the entire idea of normalization. You are creating redundancy by adding that column to the times table. This might make your queries faster but the performance will suffer. Moreover, you should not introduce redundancy just for the sake of clear SQL right now, otherwise it will become a maintenance nightmare in future. –  Rachcha Sep 2 '13 at 13:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.