Storing dates i Train schedule MYSQL

I have created a train schedule database in MYSQL. There are several thousand routes for each day. But with a few exceptions most of the routes are similar for every working day, but differ on weekends.

At this time I basically update my SQL tables at midnight each day, to get the departures for the next 24 hours. This is however very inconvenient. So I need a way to store dates in my tables so I don't have to do this every day.

I tried to create a separate table where I stored dates for each routenumber (routenumbers are resetted each day)开发者_如何学JAVA, but this made my query so slow that it was impossible to use. Does this mean I would have to store my departure and arrival times as datetimes? In that case the main table containing routes would have several million entries.

Or is there another way?

My routetable looks like this:

StnCode (referenced in seperate Station table)  
DepTime  
ArrTime  
Routenumber  
legNumber  

---

How were you storing the dates? A single date/time field? That'd certainly be the most compact representation, but also the most difficult to index and scan, especially if you're doing queries of the following type:

SELECT ...
WHERE MONTH(DepTime) = 4 AND DAY(DepTime) = 19;

Such a construct would require a full table scan to tear apart each date field and extract the month/day. For such a case, it'd be better to denomalize a bit and split the datetime into seperate year/month/day/hour/minute fields and place indeces onto them. Bit more of a hassle to maintain, but would also speed up querying by specific time parts immensely.

---

1. Instead of storing Schedules in terms of dates, you can store them against day (Sun, Mon, Tue, etc). This will eliminate storing the dates for routes. You can treat the routes as predetermined, and thus they are fixed. As the number of trains are around 8000(passenger trains) and days are fixed (7), routes are (50-1000), each table like, 1, 1A, AS PUBLISHED IN RAILWAY BOOKS,
   This will avoid storing huge combinations of train schedules into the db since every date is translated into one of the weekdays and we are not missing any data.

2. You can create a table for storing days which will have at most 7 days.

3. I would suggest to model the database in such a way, that each station is a touch point, and not as station id....

4. and you can introduce the hub concept in the design, to identify 3-4 stations, which are of the same city....

5. each station is a touch point, where it is supported by facilities like,boarding point,HALT POINT etc...

6. cause, not all the stations are boarding points for all the trains..

7. facilities are the ones which are available at different stations...

8. all facilities are not available for all the trains..., ex: Kazipet is a station, which is also a junction...but for FEW TRAINS,ON few routes, they pass thru the station, and it also halts at the station, but, it will not allow new passengers to board at the station(s). But, it will allow the same on reverse routes...