比赛/活动/战斗的模式？
我有一个 fighters 表、一个 events 表，并且我正在创建一个 fights 表。
我想跟踪比赛、所有拳击手（一对一战斗）、（可选）裁判和（可选）战斗中的裁判。这些字段是可选的，因为我并不总是有这些信息。
我应该如何为此设计架构？由于每一场战斗都有不止一场，很多，所以这是否意味着我应该创建一个映射表？例如：
fights
id event_id promotion_id
-----------------------
1 7 9
fights_fighters
id fighter_id
-----------------------
1 3
1 4
fighters
id name
-----------------------
3 Fedor Emelianenko
4 Fabricio Werdum
events
id name date
-----------------------
7 Strikeforce! Fedor vs Werdum 2010-06-26
promotions
id name
-----------------------
9 Strikeforce
像这样的模式有道理吗？由于一场战斗中只有 2 名战士，我最初只想在 fights 表中创建一个 fighter_a 和 fighter_b 列，甚至没有有一个fights_fighters 表。
有人可以深入了解拥有 Fights_fighters 映射表与仅拥有战斗机 a/b 列的优缺点吗？
我想追踪战斗如何结束。它们可以以以下任一方式结束：a) 淘汰赛 b) 决定 c) 提交 d) 停赛 - 在哪里/如何嵌入战斗结局？
I have a fighters table, an events table and I'm creating a fights table.
I want to track the event, all fighters ( 1 on 1 fights ), (optional) judges and (optional) referees in a fight. The fields which are optional are so because I won't always have this information.
How should I design the schema for this? Since each fight has more than one, its many, so does that mean I should create a mapping table? Eg:
fights
id event_id promotion_id
-----------------------
1 7 9
fights_fighters
id fighter_id
-----------------------
1 3
1 4
fighters
id name
-----------------------
3 Fedor Emelianenko
4 Fabricio Werdum
events
id name date
-----------------------
7 Strikeforce! Fedor vs Werdum 2010-06-26
promotions
id name
-----------------------
9 Strikeforce
Would a schema like this make sense? Since there are only 2 fighters ever in a fight, I initially just thought of making a fighter_a and fighter_b column in the fights table and not even have a fights_fighters table.
Could anyone offer insight into the pros/cons of having a fights_fighters mapping table vs just having the fighter a/b columns?
I want to track how fights end. They can end either in a) Knockouts b) Decisions c) Submissions d) Stoppages - where/how can I embed fight endings?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
1）正如您在 Fights_fighters 表中提到的那样，您很难执行一场战斗只有两名战士的规则。但是，很难查询包含战斗机_a 和战斗机_b 列的表来查找战斗机的所有战斗。
使用 Fights_fighters 表，您可以通过使用类型（战斗机、裁判员或法官）扩展战斗机表来添加裁判员和裁判员的条目，然后，fights_fighters 表将需要两名战斗机、零到 x 名裁判以及每名 0 到 x 名裁判员斗争。 Maye 成为一个 Fights_person 表，其中 person 表具有战斗机、裁判员或法官的类型。
2）战斗如何结束将在战斗表中。您可以仅使用带有预定义值的文本或数字字段，也可以在结果表中包含外键。如果您期望其他可能的结果（例如暂停或退出），请创建结果表，以便您可以在不更改数据库的情况下添加其他结果。另外，您是否还必须记录哪位战士赢得了战斗？
1) As you mentioned with a fights_fighters table you have trouble enforcing the rule that a fight is only two fighters. But it's difficult to query a table with fighter_a and fighter_b columns to find all the fights for a fighter.
With the fights_fighters table you could add an entry for referee and judges by expanding the fighter table with a type (fighter, referee or judge) and the fights_fighter table would then require two fighters, zero to x judges, and zero to x referees for each fight. Maye that becomes a fights_person table with the person table having types of fighter, referee or judge.
2) How the fight ends would be in the fights table. You could just use a text or numeric field with values you've predefined or you could include a foreign key to a results table. If you expect other possible results like suspended or withdrew then create the results table so you can add other results without changing the database. Also wouldn't you also have to record which fighter won the fight?