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:
- Select all legs where the train is of type
X
and in zoneY
and runs on dayZ
and have a leg where the Name of the StartStation isW
and the train has at least one leg which is (partly) betweenA
time andB
time (i.e. 8:00 - 12:00). - Select all legs from all legs which fullfil 1. and StartStation is
Q
and the StartTime is betweenC
time andD
time - Select all legs from all legs which fullfil 1. and RouteUID is
G
sorted BYStartTime
My Questions:
Do you have any improvements?
What database scheme would you recommend?
How to implement the Traveldays?
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