Database Design for Transport Schedule System

Question

It has been some time since my database design classes in my second year at Uni. and I did not do any projects in the interim, so my skills are at best rusty at the moment. I started working on a personal project using a train schedule system and seemed to be stuck in a table design that resembles something like this -

StationTbl ------------ StnName StnCity StnCode - {Primary Key} TrainTbl --------- TrnName TrnNumber - {Primary Key} SourceStn DestStn DaysofWeek TrainHopTbl -------------- TrnNumber - {Primary Key} StationCode - {Primary Key} ArrTime DepTime HopIndex 

Most fields are alphanumeric, with the exception of time and HopIndex fields in TrainHopTbl. As you can see, the preliminary design is very rude and far from complete.

Users will be able to find trains based on either the name / number of the train, or by specifying the source and destination station. The first request can be easily solved, but I am having problems writing a request for the second search, where the user gives a pair of src / dest, and the server returns a list of trains that run on this route. This information will be extracted from TrainHopTbl, which contains a list of flights for a particular train, for example:

 TrainHopTbl -------------- Num StnCode ArrTime DepTime HopIndex 121 WDC 0900 0910 1 121 BAL 1005 1010 2 121 NYC 1145 - 3 

If the user enters WDC / NYC as the src / dest pair, then the request should return train number 121 because it is a valid route.

Any recommendations on pointers / links / books on database design would be helpful. Heck, at that moment even runnable queries or entire re-projects would be useful, as I seem to be stuck in a track that I find difficult to find, and this completely stopped my progress.

Answer 1

I would take your SourceStn and DestStn from your TrainTbl - this is a useless mess.

In any case, you can get what you are looking for:

 select src.TrnNumber, srcSt.StnName as SourceStation, srcSt.StnCity as SourceCity, src.DepTime, destSt.StnName as DestinationStation, destSt.StnCity as DestinationCity, dest.ArrTime, (abs(dest.HopIndex - src.HopIndex)) as Stops from TrainHopTbl src inner join TrainHopTbl dest on src.TrnNumber = dest.TrnNumber inner join StationTbl srcSt on src.StnCode = srcSt.StationCode inner join StationTbl destSt on dest.StnCode = destSt.StationCode where src.StnCode = 'WDC' and dest.StnCode = 'NYC' and src.HopIndex < dest.HopIndex order by Stops asc, DepTime asc 

Edit: I did not account for translations here. Only direct trains are mentioned in your question. Let me know if you want a transfer, too.

Answer 2

I have not thought about this yet, so this answer may be deleted.

I think TrainHopTbl writes nodes to the network, where it would be more useful to write edges to the network. The edge will have a train number, departure station, departure time, arrival station and arrival time. And maybe a jump index like yours.

So,

Num: 121, Hopindex: 1, DepStnCode: WDC, DepTime: 910, ArrStnCode: BAL, ArrTime: 1005

He would describe the “jump” from Washington to Baltimore, a region in a network of jumps.

(In addition, I would call the jump “kicking”, but this is just a name choice.)

Due to the fact that hops connect the two stations together, it becomes possible to connect a series of jumps that will take you from one place to another in one trip. Some trips may even include changing trains at a station, provided that the arrival time is short before the departure time of the next step.

The downside of this is that there is a bit of redundancy in the station codes. I did not understand whether this redundancy is harmful or not.

Answer 3

It seems you are trying to solve a problem with a tight schedule with a database. It may be much easier to add a field to the train table, which stores the list of stops in string form

 "WDC, BAL, NYC" 

Then you just need to find the trains that contain the two substrings you are looking for, in this case "WDC" and "NYC". This narrows your search a lot, to the point that you can view the resulting trains in code outside of SQL.

Without doing more research than I am ready to do right now, you will do it

SELECT containing "WDC" and containing "NYC"

I don't know how best to use Contains ... anyone comment?