Schema for matches/events/fights?

I have a fighters table, an events table and I'm creating a fights table.

I want to tr开发者_运维问答ack 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.

1. Could anyone offer insight into the pros/cons of having a fights_fighters mapping table vs just having the fighter a/b columns?

2. 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?

---

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?