Page 1 of 1

Table design for March Madness contest

Posted: Thu Mar 06, 2003 1:39 pm
by mcurry
Hey all - I'm trying to write a March Madness bracket contest. I'm having trouble figuring out a good table design to hold the matchups and the entries. Anyone have any ideas?

Posted: Thu Mar 06, 2003 2:45 pm
by Rob the R
What about two tables: TEAMS and BRACKET. TEAMS would just hold team information (ID #, name, record, conference, rank, etc.). BRACKET would be a table with 63 rows like this:

ID (1 - 63)
ROUND
TEAM1 (ID from TEAMS)
TEAM2 (ID from TEAMS)
WINNER_BRACKET (BRACKET ID for game winner plays in)
TEAM1_SCORE
TEAM2_SCORE
WINNER_ID (ID from TEAMS)
etc. (Venue, date, time, etc.)

Each BRACKET ID would identify one game in the entire bracket, so you would pre-number the games however you like. The WINNER_BRACKET column would then be a self-reference back to the BRACKET table to identify the game the winner would play in. These would also be pre-assigned. The some of the other columns (teams, scores, and maybe some others) could be assigned only when the game was over.

For instance, a way you could number them would be as in this example from the first three rounds of the "top left" of the bracket:

Code: Select all

1--\
    --- 33
2--/      \
           ---- 49
3--\      /
    --- 34
4--/
To find out every team that could play in game #49 (the third round of the "top left" quadrant of the bracket), you could write this query:

Code: Select all

select b2.id, t1.team_name team1, t2.team_name team2
from bracket b3,
   bracket b2,
   bracket b1,
   teams t1,
   teams t2
where
   b1.winner_bracket = b2.bracket_id
   and b2.winner_bracket = b3.bracket_id
   and b3.bracket_id = 49
   and b1.team1 = t1.id
   and b1.team2 = t2.id ;
All the teams with b2.id = 33 could play the teams with b2.id = 34 in the third round.

This may not suit how you want to use the data, though. So take this example as just one possible option.