Table design for March Madness contest

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mcurry
Forum Newbie
Posts: 12
Joined: Tue Jan 21, 2003 3:41 pm

Table design for March Madness contest

Post 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?
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

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