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):
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?