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.

Basic database design

  • Are you settled on using MySQL or open to alternatives? Nov 15, 2011 at 11:37
  • Pretty much settled.. Are there any advantages/disadvantages with MySQL I should be aware of? Nov 15, 2011 at 17:58
  • Check constraints aren't enforced. Generally fewer options for enforcing constraints with DRI - but whether that matters to you depends very much on your application. Happy to chat if you want more opinion :) Nov 15, 2011 at 18:04
  • Thanks, but I don't think I would've use constraints anyway since I'm not very familiar with it. Will validate all data in my application before it's sent to the DB, keeping it simple Nov 15, 2011 at 18:57
  • Good good. It's simpler in the DB of course but that is a whole other conversation ;) Nov 15, 2011 at 19:01

3 Answers 3


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.


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

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 -----+

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


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.