What would be the relational way to store a tournament bracket in MySQL?

Question

Right now I have a table for matches where each match has a reference to 2 competing participants and a winner, but there isn't a way to store the order of the rounds.

Answer 1

Is there anything stopping you from creating a field named "round_order"? Do you know the order?

Answer 2

Simply storing a "round number" field in the matches table seems to be sufficient. You may want to assign the round numbers in reverse order, ie: 1 for the final, 2 for the semi-finals, 3 for the quarter-finals, and so on, instead of 1 for the first round, 2 for the second, etc.

This has the added advantage where you can immediately derive information from the round number. For example doing a query for all the semi-finals will be very easy ... WHERE round_number = 2, and will be independent from the total dept of the tournament rounds.

Answer 3

Actually, I'm not sure I agree.

If you have a "round" field in the "match" table, you introduce the possibility of integrity problems, as you end up with a row in your table for each round (which doesn't really make sense as this is the "match" table, so there should only be one row per match)

If you do that, each row could potentially have a different set of participants in it, which is likely not your intention and indicates that you should key "round" out to a different table. Other integrity problems introduced would be the "Winner field" e.g. if you list the winner of the round in the match tables, how do you know who won the match? If you just put the winner of the match across all rows, you lose the information about who won the round.

To be appropriately normalised, create, say a "MatchRound" table.

Give your Match table a primary key (call it Match_Id for example) and then your Round table could have: Round_Id (autonumber or similar unique primary key), Match_Id (which is a foreign key to the match table) and perhaps RoundOrder and RoundType (Final, Semi, etc). You're probably also okay adding Winner and Loser to this table.

While dual-purposing the round_number field for semis/quarters etc would work, it only works if the topology is consistent across matches, e.g. there are always quarter finals? Some sports accumulate points for example, and then have only a final. For this reason, I'd just create RoundType text field.

Answer 4

Is the bracket seeded, like in Tennis (or the March Madness tournament), and if so, will you need to answer questions like "In what round, if at all, will Federer and Nadal play each other?" Then you'd need to store the bracket as a tree in the database.