mardi 3 mars 2015

Databasedesign for Railway System


This is how i indent to design my database: (It is for a academic project)



Table Trains:
TrainUID # primary key
TrainNumber # Official train number do not need to be unqiue
Traveldays # This is currently given as bitfield with a span over a year
TrainType # ICE, RE, ...
TrainZone # depending on where they run

Table Legs:
LegUID # primary key
TrainUID # foreign key to Trains
RouteUID # foreign key to Route
StartTime # Time when the train leaves i.e. 10:30
EndTime # Time when the train starts i.e. 10:33
TravelTime # Time the trains runs
DwellTime # DwellTime at endstation

Table Routes:
RouteUID # primary key
StartStation # foreign key to Station, Station where the leg begings
EndStation # foreign key to Station, Station where the leg ends

Table Stations:
StationUID # primary key
StationName # name of station
StationLongitude # gps
StationAlditude # coordinates


I need to make querys of the following type fast:



  1. Select all legs where the train is of type X and in zone Y and runs on day Z and have a leg where the Name of the StartStation is W and the train has at least one leg which is (partly) between A time and B time (i.e. 8:00 - 12:00).

  2. Select all legs from all legs which fullfil 1. and StartStation is Q and the StartTime is between C time and D time

  3. Select all legs from all legs which fullfil 1. and RouteUID is G sorted BY StartTime


My Questions:




  1. Do you have any improvements?




  2. What database scheme would you recommend?




  3. How to implement the Traveldays?




  4. Query #1 is called only once and first, while Query #2 and #3 is called very often. Can you take advantage of the fact that #2 and #3 is always a subset of #1?







Aucun commentaire:

Enregistrer un commentaire