If there's some SQL-gurus here, I'd like some insight...
I'm thinking about creating a set of tables to store the timetables of a bus network. There's a catch, that's not for an actual use, but I still want something usable for the same kind of usage.
Mostly, I need to be able to:
- given a stop name, listing all lines that go there
- given a time, a stop name, a line and a direction, get the next time the bus will stop there
- list all the following times and stops of the previous bus, till the end of the line
Here is my current take on this:
1) A table that store bus stops names
TABLE STOPS
int StopID
char[30] Name
where primary key is StopID
2) A table that stores times
TABLE TIMES
int LineID
int BusID
time Time
where primary key is LineID, BusID
I would use BusID not to identify a bus (again, I won't actually use it in real world, so I don't need bus management), but only a single "travel" of a bus: the BusID would change when the bus reverse at terminus.
So, here are my questions (I know it's mostly a matter of preferences, and that all solution could work):
In my current take, two buses on two different lines can share the same BusID. I could use different BusID over all the lines, so I wouldn't need both LineID and BusID in TIMES, as long as I add a third table that link BusID to LineID (or use some digits of BusID to store the line, such as 5007 for the 7th bus in the 5th line). Would this be better?
Would you add a ROUTE table to be able to get all the stops of a line more easily? (I mean, I can get the route by retrieveing the timetable of a given bus in TIMES... although in real situations, several buses (such as the first or last one) won't do the complete route, so choosing the right "bus" may be tricky)
I can use the parity of BusID to get the direction (A to B or B to A) in the line... Would it be better to explicitly use an additional boolean column (and then, in primary, or not?) Or use different LineID for both directions?
Any other advices?