模式标准化 :: 受团队约束的复合比赛时间表
与问题的原始广义版本相关：http://stackoverflow.com/questions/6068635/database-design-normalization-in-2-participant-event-join-table-or-2-column
正如您所见在上面的线程中，一场比赛（事件）被定义为恰好有 2 支球队（参与者）在给定日期互相比赛（没有球队每天互相比赛超过一次）。
在我们的例子中，我们决定使用一个复合时间表，其中包含 gameID PK、球队的 2 列（称为 team1 和 team2）以及比赛日期、时间和比赛日期。位置列。此外，由于两个团队+日期必须是唯一的，因此我们在这些组合字段上定义了一个唯一键。另外，我们有一个球队表，其中的 teamID PK 与日程表列 team1 和 team1 相关。 team2 通过 FK。
这个模型对我们来说效果很好，但我在上面的帖子中没有发布的是预定比赛和结果之间的关系，以及处理每个球队的预定比赛的“版本”（即球队1或球队2想要包含的任何注释，例如，“这是一场针对非分区对手的混战，不会计入联赛积分榜”）。
我们当前的表模型是：
团队>综合时间表>结果>统计数据（得分和防守表）
团队>玩家
团队>团队日程*
*破解处理注释问题并允许进行 TBD/TBA 游戏，其中对手、日期和/或位置在提交时间表时可能不知道。
我忍不住想我们可以巩固这个模型。例如，真的需要一个单独的结果表吗？综合赛程难道不能同时是赛程表和比赛结果吗？这就是连接表可以发挥作用的地方。
连接表实际上是一个 gameID 生成器，由以下部分组成：
游戏ID（PK）
比赛日期
游戏时间
位置
那么修改后的综合时间表/结果将是：
ID（主键）
teamID（FK 到团队表）
gameID（FK加入桌子）
游戏类型（混战、锦标赛、季后赛）
得分（即进球数）
处罚
权力游戏
结果（胜负平局）
笔记（团队版本的游戏）
想法赞赏，试图深入到中心问题是很棘手的（因此上面是原始问题）
Related to the original generalized version of the problem:http://stackoverflow.com/questions/6068635/database-design-normalization-in-2-participant-event-join-table-or-2-column
As you'll see in the above thread, a game (event) is defined as exactly 2 teams (participants) playing each other on a given date (no teams play each other more than once in a day).
In our case we decided to go with a single composite schedule table with gameID PK, 2 columns for the teams (call them team1 & team2) and game date, time & location columns. Additionally, since two teams + date must be unique, we define a unique key on these combined fields. Separately we have a teams table with teamID PK related to schedule table columns team1 & team2 via FK.
This model works fine for us, but what I did not post in above thread is the relationship between scheduled games and results, as well as handling each team's "version" of the scheduled game (i.e. any notes team1 or team2 want to include, like, "this is a scrimmage against a non-divisional opponent and will not count in the league standings").
Our current table model is:
Teams > Composite Schedule > Results > Stats (tables for scoring & defense)
Teams > Players
Teams > Team Schedule*
*hack to handle notes issue and allow for TBD/TBA games where opponent, date, and/or location may not be known at time of schedule submission.
I can't help but think we can consolidate this model. For example, is there really a need for a separate results table? Couldn't the composite schedule be BOTH the schedule and the game result? This is where a join table could come into play.
Join table would effectively be a gameID generator consisting of:
gameID (PK)
gameDate
gameTime
location
Then revised composite schedule/results would be:
id (PK)
teamID (FK to teams table)
gameID (FK to join table)
gameType (scrimmage, tournament, playoff)
score (i.e. number of goals)
penalties
powerplays
outcome (win-loss-tie)
notes (team's version of the game)
Thoughts appreciated, has been tricky trying to drilldown to the central issue (thus original question above)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为没有任何理由为时间表和结果设置单独的表格。但是，我会将“gameType”移至 Games 表，否则您将存储相同的值两次。我还考虑将 teamID 添加到 Games 表中。这将有两个目的：它使您能够轻松区分主队和客队，并且使编写在同一行上返回两队数据的查询变得更加容易。
如图所示，我还会省略“结果”字段。这可以有效且高效地从“分数”列中得出。
I don't see any reason to have separate tables for the schedule and results. However, I would move "gameType" to the Games table, otherwise you're storing the same value twice. I'd also consider adding the teamIDs to the Games table. This will serve two purposes: it will allow you to easily distinguish between home and away teams and it will make writing a query that returns both teams' data on the same row significantly easier.
As shown, I would also leave out the "Outcome" field. That can be effectively and efficiently derived from the "Score" columns.