Best way to design tournament database

Question

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.

I have created a mockup below, which i'm pretty satisfied with when it comes to storing the results of all "known" group stage matches. This design makes it very easy to check if a user has placed a correct bet or not.

But what is the best way to store the quarter and semi finals? Those matches depend on the outcome in the group stage.

One approach I thought of was adding ALL matches to the matches table, but assign different variables or identifiers to the home/away teams for the matches in the knockout phase. And then have some other table with those identifiers mapped to teams... This could work, but does not feel right.

Answer 1

I'd start off by trying to fix all the predetermined information in the model itself including

• dates/venues
• structure (ie group/knockout stages)
• rules (ie points scoring, tie-break rules)

Some of this information will be data in tables, some will be codified logic in views.

Something like this perhaps:

• team(team_id, group_code enum('A', 'B', 'C', 'D'), name)
• match(match_id, kickoff_at)
• group_match(match_id, team_id_home, team_id_away, group_code)
• knockout_match(match_id, knockout_code enum('Q1', 'Q2', 'Q3', 'Q4', 'S1', 'S2', 'F')
• result(match_id, score_home, score_away)

Information such which teams play in Q1 never needs to be stored directly because it can be calculated from the group stage results. The only changes to make as the tournament progresses are inserts into the result table.

Answer 2

I think that using the team ID is the right way to go. Another level of abstraction for all of the finals rounds just adds unnecessary complexity for not much benefit other than pre-loading the matches table with data.

The data structure looks pretty solid to support this. The quarter and semi finals would need to be added to the matches table once the initial match results are in. If the matches are assigned randomly then this is a manual operation, however, if they are in a particular order...

   A
match 1 -----+
   B         A
          match 5 -----+
   C         C         |
match 2 -----+         |
   D                   A
                    match 7
   E                   F
match 3 -----+         |
   F         F         |
          match 6 -----+
   G         G
match 4 -----+
   H

...then this could possibly be done with a query. Again, the complexity of the query may not be worth the effort depending on the number of teams

Answer 3

It's a good idea to store all matches in the table "matches". However I would add an aditional field "ranking" to it, because later you need it to build a binary tree to efficiently query the table in memory. It's a classic ranking algorithm problem and you can google for gray code tournament for more information or look in my stackoverflow history. Basically a tournament is a binary tree. Here is a good article about gray codes: http://villemin.gerard.free.fr/Wwwgvmm/Numerati/CodeGray.htm. Unfortunately it's french. Here is how to generate a binary tree from the ranking: http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/229068.