如何对表层次结构建模
我正在尝试制作一个有关公式 1 的应用程序。我有三个表：车队、车手和比赛结果。我正在考虑三个选项（也许我错过了更多）：
有一个派生表 Driver_Team。该表中有一个 Driver_TeamId。在比赛结果表中使用该 Driver_TeamId。这似乎解决了我认为我将要使用的大部分查询，但感觉很尴尬，而且我在任何地方都没有看到它。
比赛结果表中有 Driver.DriverId 和 Team.TeamId。这存在无法添加额外信息的问题。我还不知道什么信息，也许是加入新团队的开始日期。然后我需要一个连接表（因为该信息与比赛结果无关）。
最后一个：有一个连接表Driver_Team，但在Race Results表中只有Driver.DriverId作为外键。问题是，像“x 队在 y 赛季/几个赛季中得到了多少分”这样的查询真的非常可怕。
我是否缺少另一个解决方案？如果是，请告诉我！ :-) 否则，这些解决方案中哪一个似乎最好？
谢谢！
I'm trying to make an application about formula 1. I have three tables: Team, Driver, And Race Results. I'm thinking about three options (and maybe I'm missing more):
Have a derived table Driver_Team. Have a Driver_TeamId in that table. Use that Driver_TeamId in the Race Results table. This seems to solve most of the queries I think I am going to use, but feels awkward and I haven't seen it anywhere.
Have Driver.DriverId and Team.TeamId in the Race Results table. This has the problem of not being able to add extra information. I don't know yet what information, maybe the date of the start of joining a new team. Then I would need a junction table (because that information is not Race Result related).
The last one: Have a junction table Driver_Team, but have only the Driver.DriverId as Foreign Key in the Race Results table. Problem is, queries like "How much points did team x get in season y/several seasons" really really horrible.
Am I missing another solution? If yes, please tell me! :-) Otherwise, which of these solutions seems the best?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
只是集思广益，一个对象模型可能看起来像这样。请注意 RaceResult 上明显缺少“id”字段，因为终点位置完美地充当了自然键（每个终点位置有一名车手）。当然，可能还有很多其他选择。
Just brainstorming, one object model may look like this. Note the conspicuous lack of an "id" field on RaceResult, as the finishing position acts perfectly as a natural key (one driver per finishing position). Of course, there may be lots of other options as well.
对于您正在谈论的查询类型，我认为 DriverId 和 TeamId 都应该在 RaceResults 中。如果您想存储有关车手和车队之间关联的附加信息，则应将其放置在单独的表中。这似乎造成了一点冗余，因为比赛表中的车手/车队对将受到 DriverTeam 表中的雇佣日期的限制，但考虑到合同和时间表的复杂性，我认为最终可能不会特别重要多余的。
我喜欢您规划数据库来支持查询的方式。多年来，我在数据库设计中遇到了太多的 OOP 思维！
For the kind of queries you're talking about, I think DriverId and TeamId should both be in RaceResults. If you want to store additional information about an association between a driver and a team, then that should be placed in a separate table. This appears to create a little bit of redundancy, since the driver/team pair in the race table will be limited by the employment dates in the DriverTeam table, but given the complexities of contracts and schedules, I think it may end up being not especially redundant.
I like the way you are planning the DB to support your queries. I have run into way too much OOP thinking in DB design over the years!
如果您仅将 DriverId 和 TeamId 存储在 RaceResults 表中，则无法将车手与没有 RaceResult 的车队关联。
If you only store DriverId and TeamId in the RaceResults table, then you cannot associate a driver to a team without a RaceResult.
你的第一个选择得到了我的投票。我还建议添加一个 Race 表（用于保存赛道、日期、条件等数据），并使 Race_Results 成为 Driver_Team 和 Race 的组合。
Your first option gets my vote. I'd also suggest adding a Race table (to hold data such as track, date, conditions, etc.), and make Race_Results the combination of Driver_Team and Race.
我建议如下：
RaceResult - Driver - DriverTeam - Team
其中 RaceResult 包含
race_date，DriverTeam 包含 (driver_id,team_id,team_join_date< /code> 和team_leave_date）。然后，即使查询可能很复杂，您也可以获得问题中询问的所有信息。I suggest the following:
RaceResult - Driver - DriverTeam - Team
Where RaceResult contains
race_date, DriverTeam contains (driver_id,team_id,team_join_dateandteam_leave_date). Then you would be able to get all the info you're asking about in your question, even though the queries may be complicated.