Database Design for Transport timetable system

Question

Its been a while since my Database Design classes in my sophomore year at Uni. and I haven't done any designs in the interim so my skills are at best rusty at the moment. I have begun working on a personal project involving the railway timetable system and seem to be stuck at the table design which 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 alphanumberic with the exception of the Time fields and the HopIndex in TrainHopTbl. As you can see the preliminary design is very crude and far from finished.

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

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 query should return Train Number 121 since it is a valid route.

Any pointers/links/book suggestions on database design would be helpful. Heck, at this point even runnable queries or entire re-designs would be helpful since I seem to be stuck in a rut that I am finding hard to get out of and this has completely stalled my progress.

Answer 1

I'd take your SourceStn and DestStn out of your TrainTbl -- it's needless clutter.

Anyway, you can get what you're looking for with:

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 haven't taken into account transfers here. Your question mentioned just straight route trains. Let me know if you want transfers, as well.