[thelist] An intellectual exercise: NCAA pool

Howard Cheng howcheng at ix.netcom.com
Mon Mar 25 15:50:00 CST 2002


Hello all,

During this season of March Madness, as the NCAA college basketball
tournament is known (for you non-Americans), I thought it might be fun to
program a little web application to handle a tournament pool. Although I'm
sure said applications already exist, it still would be fun to do just to
do it.

Pool rules:
- $X/person entry fee.
- Winners are determined by highest total points.
- 2^[round #] pts for every correct team picked. Thus, for each winner of a
first-round game, player gets 2^1 (= 2) pts. Round 2, you get 4 pts each;
Sweet 16 (round 3), 8 pts; Elite 8, 16 pts; Final 4, 32 pts; Championship,
64 pts.
- First place winner gets 50% of the pot. Second place: 30%. Third place:
15%. Administrator gets 5% for doing the work.

I've already come up with a database schema that works pretty well. My
vision is that as the pool administrator, one would only have to enter the
teams by their bracket and seeding, and the matchups would automatically be
determined. The only problem I can't figure out is, after the first round,
how would one determine who a team's next opponent would be?

Round 1 games are always the same:
[top half of bracket]
1 vs 16
8 vs 9
5 vs 12
4 vs 13
[bottom half of bracket]
3 vs 14
6 vs 11
7 vs 10
2 vs 15

So just by entering the teams and their seeds, we would be able to figure
out what all the first round games are because the schedule is always the
same. But after round 1, if we input the winners (assume all the higher
seeds advance), how would I know (in a script) that in round 2, #1 should
play #8?

Perhaps a look at the proposed database schema might shed some light.

Table Pools -- for managing multiple pools
id [pk]
name

Table Players
id [pk]
first_name
last_name
email

Table PlayersPools -- players may play in multiple pools
player_id [pk][fk]
pool_id [pk][fk]
amt_paid

Table Rounds -- validation table for the tournament rounds
id [pk]
name (e.g., "Sweet 16")

Table Entries -- entries are a one-to-many relationship to players
id [pk]
player_id [fk]
pool_id [fk]
name (e.g., "My First Pool Entry")
score

Table Teams
id [pk]
bracket
seed

Table Picks
entry_id [pk][fk]
team_id [pk][fk]
round_id [pk][fk]

Table Results -- this holds the actual tournament results
team1_id [pk][fk]
team2_id [pk][fk]
round_id [fk]
score
winner [fk]

My imagined scripting model would be like (it's a bit on the fuzzy side):

The administrator has one page to enter the results of the current round.
On submit of that form, we insert those into the Results table. Then we
select all the winning teams for that round. Loop through the winners. On
each iteration, select the entries where that team in that round was picked
and increment the score.

Now, as the administrator, for the next round, I would want to be presented
with all the matchups automatically, but again, I can't figure out how the
database would know who plays whom in round 2.

You know, this was a lot clearer last night while I was ruminating through
it, but I seem to have lost my vision somewhat overnight. And since the
tournament is already almost over this year, this obviously wouldn't be
programmed until next year, but I wanted to run with it now while it was
still (somewhat) in my head.


::::::::::::::::::::::
Howard Cheng
howcheng at ix.netcom.com
AIM: bennyphoebe
ICQ: 47319315




More information about the thelist mailing list