Database Design for one-on-one sports

Question

Let's say that I'm developing an application to track boxing results. Is the best way to track these to have two tables, like so:

Boxers
======
id
name

Matches
=======
id
match_number
boxer_id
opponent_id
result

... and then have two records for each match, one for the winner, and one for the loser, something like this:

id match_number boxer_id opponent_id result
-- ------------ -------- ----------- ------
1  1            1001     2001        WIN
2  1            2001     1001        LOSS

... or am I missing a better way to do this?

What if there wasn't a result stored, just a recording that the two opponents were matched up?

Thanks!

Answer 1

Store the winner_id in the same record.
This allows you to show upcoming fights, unlike solutions that suggest storing winner_id and loser_id only as no winner or loser exists before a fight takes place.

Matches

• match_id
• match_date
• winner_id

Match Fighters

• match_id
• fighter_id

Fighters

• fighter_id
• fighter_name

Answer 2

Well, for a fully normalized, I'd go with:

Boxers - as cited.

Matches:

id  / match_number   (Don't think these need to be distinct anymore)
winner (FK-Boxers; Nullable, for for matches scheduled, but not decided yet)

Opponents:

id  (meaningless PK value.  Optional. 
     you could use the other two fields as the PK)
match_id
boxer_id

Two rows in Opponents and one in Matches for each match.

(Updated to add suggestions from other answers)

Answer 3

Storing the winner ID would cut the number of records in the Matches table in half. Otherwise it doesn't make sense to store the opponent ID.

Answer 4

Boxers as in the question

Matches with:

match_id int pk
winner_id int fk
loser_id int fk
result(_id) int/bool nullable
date
...

If the result can only be "win" or "unknown", a nullable boolean is sufficient.

If there are different result types (e.g. the name of the technique that led to the win/loss in sumo), use a nullable int.

When you store data, you should always ask yourself: what kind of questions should my data be able to answer.