父子关系：多表与自连接
我正在为一家体育俱乐部开发一个统计数据库，帮助管理一些赛事。我想我已经有了跟踪竞争对手名称、分类、类别等的部分，使用一个主表、几个查找表和几个桥接表来涵盖所涉及的多对多关系。
我已经为下一部分勾勒出一个模型，但我对自己做得是否正确感到不太满意。我知道我们如何在纸上和/或电子表格中做到这一点，我仍在做大量的手动工作，但我不是 100% 有信心将其正确“翻译”为表格和关系。
一个简单的事件结构可能如下所示：
Tournament
- Day 1
- Match 1 (Fired Match)
- Match 2 (Fired Match)
- Match 3 (Fired Match)
- Match 4 (Aggregate Match of 1,2,3)
- Day 2
- Match 5 (Fired Match)
- Match 6 (Fired Match)
- Match 7 (Fired Match)
- Match 8 (Aggregate Match of 5,6,7)
- Match 9 (Aggregate Match of 4,8)
我将事情进一步分解，并有“分数”、“阶段”、“比赛”和“锦标赛”（等等）的表格。这个想法是，分数是我需要为每个竞争对手记录的原始数据，以及“竞争对手”和“阶段”表的外键。我决定将其视为一个或多个阶段的聚合，而不是“解雇”和“聚合”比赛，并且锦标赛可以有一场或多场比赛（聚合）。
我遗漏了其他外键指示的一些查找表，因为它们与以下问题没有直接关系我猜是“这是解决这个问题的正确方法吗，还是我最好跳过比赛/阶段分离？是否有一种方法可以在单个表中重新实现比赛和阶段之间的父子关系通过自加入？
I'm working on a database for stat keeping for a sports club where I help administrate some events. I think I've got the parts for keeping track of the competitor names, classifications, categories, etc. figured out using a main table, several lookup tables and a couple bridge tables to cover the many-to-many relationships involved.
I have a model sketched out for the next part, but I'm not entirely comfortable that I'm doing it right. I know how we do it on paper and/or in a spreadsheet where I'm still doing a lot of manual work, but I'm not 100% confident I'm 'translating' it to tables and relations correctly.
A simple event structure might look like this:
Tournament
- Day 1
- Match 1 (Fired Match)
- Match 2 (Fired Match)
- Match 3 (Fired Match)
- Match 4 (Aggregate Match of 1,2,3)
- Day 2
- Match 5 (Fired Match)
- Match 6 (Fired Match)
- Match 7 (Fired Match)
- Match 8 (Aggregate Match of 5,6,7)
- Match 9 (Aggregate Match of 4,8)
I had broken things down a bit further and have tables for 'scores', 'stages', 'matches', and 'tournaments' (among others). The idea being that scores are the raw data I need to record for each competitor, with foreign keys to the 'competitor' and 'stages' table. Rather than having 'fired' and 'aggregate' matches, I decided to approach it as matches being aggregates of one or more stages, and tournaments can have one or more matches (aggregates).
I left out some of the lookup tables indicated by the other foreign keys since they don't directly relate to the matter at hand which I guess is "Is this the correct way to go about this, or would I be better off to skip the matches/stages separation? Would there be a way to reimplement this parent-child relationship between matches and stages in a single table via a self-join?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对我来说，它看起来像是一个适当规范化且灵活的层次结构。
按照设计，它意味着一场锦标赛有一场或多场比赛，一场比赛有一个或多个阶段……或者，更重要的是，每个父母都有可变数量的孩子，其中数量始终大于零，但可以并且将会因实例而异。如果情况并非如此，如果父级（锦标赛或比赛）始终具有相同数量的子级，那么对数据进行非规范化可能会带来一些小优势（例如工作日有七列的表），但这可能会从长远来看会很尴尬。
我绝对不会使用自连接来创建单个表，因为层次结构的不同级别包含不同的属性。例如，“镜头”是舞台的一部分，而不是匹配的一部分，因此不应成为匹配表的一部分。
It looks like a properly normalized and flexible hierarchical structure to me.
As designed, it implies that a Tournament has one or more Matches, and a Match has one or more Stages... or, more to the point, each parent has a variable number of children, where the number is always greater than zero but can and will vary from instance to instance. If this is not the case, if a parent (Tournament or Match) will always have the same number of children, then there might be some minor advantage in denormalizing the data (like a table with seven columns for the weekdays) , but that may prove awkward in the long run.
I would definitely not do a single table with a self join, as the different levels of the hierarchy contain different attributes. For example, "Shots" is part of a Stage and not part of a Match, and so should not be made part of the Match table.