Train timetable database

So for some fun I'm working on a timetable database for a train system. Here's what I have so far for the table (Use a little imagination for the data types):

Quote:

---

STATIONS:
stationID
stationName

PLATFORMS:
platformID
stationID (Links to the STATIONS table)
platformNumber (Each station has Platform 1, Platform 2, Platform 3 etc, so the platformID is used in the timeIndex)

TIMES:
timeIndexID
timeIndex(Time of day)
timeDay(Weekdays/Weekends etc)
sourcePlatformID
destinationPlatformID

---

Each station has multiple platforms, each platform can go to any other platform on another station depending on the time, and not all platforms or stations link together directly.

Basically, the only way I can think to be able to calculate a route to another station that isn't directly connected to your point of origin is to check every possible timeIndex and keep looping and listing until you find the path to your destination. That, or program every possible loop into the database.

Anyone else have any suggestions to make this a little faster/less mind bending/less beating the SQL server?

Is it actually possible for each station to be used to change trains? If not (such as some stations are just on a line between two other stations, and have no other lines), one thing you could do is add a boolean field to the Stations table to say whether each station can be used for changes. This should allow an exponential speed improvement, as many stations could be ignored at each stage.


What I guess the real train companies use for this kind of thing is a table containing a list of all valid source and target stations/platforms where the journey cannot be made directly, along with a "journey ID" which is used to refer to another table which lists the stations to change at (with another field to denote the order within that journey).

Doing this would not only allow you to ignore the no-change stations as above, but would also allow you to ignore any that are in the wrong direction (eg: a station to the south when you want to go north).

If you also store a minimum time for each of these journeys, you could calculate them in order (lowest first) and completely skip any further ones which have a minimum time that is higher than your current best. By using this method, the "quality" of the data in the table isn't too important - you can actually store every possible journey.

I think I got what you mean.

Quote:

---

STATIONS:
stationID
stationName

PLATFORMS:
platformID
stationID (Links to the STATIONS table)
platformNumber (Each station has Platform 1, Platform 2, Platform 3 etc, so the platformID is used in the timeIndex)

TIMES:
timeIndexID
timeIndex(Time of day)
timeDay(Weekdays/Weekends etc)
sourcePlatformID
destinationPlatformID

JOURNEYS:
journeyID
journeySource
journeyDestination
journeyTime

LINKUP:
linkID
linkStopIndex (The amount of stops since source platform from current position)
journeyID
sourcePlatformID (Your current stop)
destinationPlatformID (Platform of next stop)

---

You would run a script that calculates every possible journey everytime you add in another station, platform, train etc so it populates the JOURNEYS and LINKUP table. So for example:

http://img243.imageshack.us/img243/4...instations.png

To get from Pizza to Fruit it would look like this

Quote:

---

journeyID = 7
journeySource = Pizza
journeyDestination = Fruit
journeyTime = 0700

---

http://img833.imageshack.us/img833/5889/linkso.png

It wouldn't have multiple paths, because it would choose the fastest path between the 2 points given the leaving time (journeyTime).

It could workout when you have to change platforms etc from the other tables.

Is this what you meant?

That's it. :)

I'm not sure how good it will be, but it certainly seems to be a valid way to do it, and I haven't thought of anything better.

Another approach would be to treat rail segments as you would hotel rooms... reservation based. That way you ensure no two trains (the clients) use the same segment at the same time for safety reasons. This design also facilitates reallocation of rail segments (or rerouting) in case of accidents/damage to track. You would have to store though what segments are connected to each other and in what direction this is.