Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Nov 14 '11 at 22:52

This question came from our site for professional and enthusiast programmers.

    
Are you settled on using MySQL or open to alternatives? –  Jack Douglas Nov 15 '11 at 11:37
    
Pretty much settled.. Are there any advantages/disadvantages with MySQL I should be aware of? –  hamohl Nov 15 '11 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 :) –  Jack Douglas Nov 15 '11 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 –  hamohl Nov 15 '11 at 18:57
    
Good good. It's simpler in the DB of course but that is a whole other conversation ;) –  Jack Douglas Nov 15 '11 at 19:01
add comment

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
Maybe be not, but the group stage itself is a binary tree. Why make an extra table? Can't you just add a ranking_grp field? –  Phpdna Nov 15 '11 at 13:15
1  
How do you model the group stage (which is a league - all teams play each other) as a binary tree? –  Jack Douglas Nov 15 '11 at 13:32
    
I would think of a kd-tree to model the group stage. –  Phpdna Nov 15 '11 at 14:17
    
This might be a good solution! Thank you for the hands-on example. @David how would you do your data model? Not sure I fully understand what you are suggesting, you speak in terms above my paygrade... :) –  hamohl Nov 15 '11 at 17:42
    
Given that I was the first to come up with the formula to calculate the stages I disagree with Jack. And I don't see a model for grp stage either. Do you see it? –  Phpdna Nov 15 '11 at 18:14
show 12 more comments

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

share|improve this answer
add comment

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.

share|improve this answer
1  
-1 for optimisation to "efficiently query the table". This table would need several orders of magnitude more rows first –  Jack Douglas Nov 15 '11 at 6:55
    
I mean build a binary tree in memory. Is that difficult to understand? –  Phpdna Nov 15 '11 at 7:01
    
Matches do need to be related to each other in the model, but it is nothing to do with efficient querying. Note that there is a 'group' stage followed by a knockout stage. –  Jack Douglas Nov 15 '11 at 8:03
    
In my anwser I explain that he need a ranking field. Ranking field+gray code = depth of binary tree = stages. –  Phpdna Nov 15 '11 at 8:11
1  
I see, you are not talking about query efficiency then. Just a translation issue :) –  Jack Douglas Nov 15 '11 at 9:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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