运输时间表系统的数据库设计

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.

大多数字段都是字母数字,除了时间字段和TrainHopTbl中的HopIndex。正如您所看到的,初步设计非常粗糙,远未完成。

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 -

用户将能够根据列车名称/号码或通过指定源站和目的站来查找列车。第一个查询可以很容易处理但是我在为第二个搜索编写查询时遇到问题,其中用户给出src / dest对,服务器返回在该路由上运行的列表列表。这些信息将从TrainHopTbl中提取,其中包含特定列车的啤酒花列表,如下所示 -

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.

如果用户输入WDC / NYC作为src / dest对,则查询应返回列车号121,因为它是有效路线。

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.

关于数据库设计的任何指针/链接/书籍建议都会有所帮助。哎呀,在这一点上,即使是可运行的查询或整个重新设计也会有所帮助,因为我似乎陷入了一种难以摆脱的困境,这完全阻碍了我的进步。

3 个解决方案

#1

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

我将你的SourceStn和DestStn从你的TrainTbl中取出 - 这是不必要的混乱。

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.

编辑:我没有考虑到这里的转移。你的问题只提到直线列车。如果您想转账,请告诉我。

#2

I haven't thought this through at all yet, so this response could be way off.

我还没有想到这一点,所以这种反应可能会有所不同。

I think the TrainHopTbl records the nodes in a network, where it would be more useful to record the edges in a network. An edge would have a train number, a departure station, a departure time, an arrival station, and an arrival time. And maybe a hop index like the you have.

我认为TrainHopTbl记录了网络中的节点,在网络中记录边缘会更有用。边缘将具有火车号码,出发站,出发时间,到达站和到达时间。也许像你一样的跳跃指数。

So,

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

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

Would describe the "hop" from Washington to Baltimore, an edge in the network of hops.

将描述从华盛顿到巴尔的摩的“跳跃”,这是啤酒花网络的一个优势。

(Also, I would call a hop a "leg", but that's just naming choice.)

(另外,我会称一个跳跃为“腿”,但这只是命名选择。)

By having the hops tie two stations together, it becomes possible link up a series of hops that gets you from one place to another in a single trip. Some trips could even involve changing trains a some station, provided the arrival time is a little before the departure time for the next hop.

通过让啤酒花将两个站点连接在一起,可以连接一系列的啤酒花,让您在一次旅行中从一个地方到另一个地方。有些旅行甚至可能涉及将火车换成某个车站,前提是到达时间稍早于下一站的出发时间。

The down side to this is that there's a little more redundancy in the station codes. I haven't figured out whether this redundancy is harmful or not.

这方面的缺点是站点代码有一点冗余。我还没弄清楚这种冗余是否有害。

#3

It seems like you are trying to solve a hard graph problem with the database. It might be much easier to add a field to the train table that stores the list of stops in a string form

您似乎正在尝试解决数据库的硬图问题。将字段添加到火车表中可能要容易得多,该火车表以字符串形式存储停靠点列表

"WDC, BAL, NYC"

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

然后你只需要找到包含你正在寻找的两个子串的列车,在这种情况下是“WDC”和“NYC”。这会大大缩小您的搜索范围,直到您可以在SQL之外的代码中考虑结果列车。

Without doing more research than I am willing to do right now, what you do would then do is

如果没有比我现在愿意做更多的研究,那么你所做的就是

SELECT from where contains "WDC" AND contains "NYC"

SELECT从哪里包含“WDC”AND包含“NYC”

I don't know the best way of doing Contains ... comments anyone?

我不知道做包含的最佳方式...评论任何人?

#1