help with relational database design and implementation

[hanaleiroll]

Hi,

I need help with laying out my database. I'm trying to make a game where users can login and pick winners in a surf contest. There are 3 rounds in the surf contest and the heat draws are calculated by the surfers seed (ranking), 48 surfers in all.

 

Round 1 (16, 3 man heats)

HEAT 1

o  surfer (seed #10)

o  surfer (seed #29)

o  surfer (seed #40)

 

HEAT 2

o  surfer (seed #9)

o  surfer (seed #30)

o  surfer (seed #39)

 

...etc. So in Round 1, you pick the guy you think will win by selecting the radio button. Then, I need to track the users selection somehow and send the winners into round 3 while the 2 losers go to round 2. Then round 2 page comes up:

 

Round 2 (16, 2 man heats)

HEAT 1

o  surfer (seed #1)

o  surfer (seed #32)

 

HEAT 2

o  surfer (seed #2)

o  surfer (seed #31)

 

...etc. So, Round 2 is just like Round 1 where you pick your winner by radio button and the winner goes into round 3 while the loser is eliminated. Part of the trick is: If the number 1 seed wins his round 1 heat and moves directly into round 3, then the #2 seed (if he loses his round 1 heat and moves into round 2) becomes the #1 seed. So I need to track which seeds are in the round and sort them and re-seed them in ascending order, then call them into their respective heats based on the seed matchups.

 

Round 3 is the result of the round 1 winners and round 2 winners. The heat draw would just be displayed, but no option of picking winners is to be offered. It would just look like this:

 

This is Your Round 3 Heat draw:

 

HEAT 1

surfer (seed #3)

surfer (seed #9)

 

HEAT 2

.....

 

So thats the idea. This is what I have for my database so far:

 

user_tbl

---------------

userID

user_email

user_password

 

surfer_tbl

---------------

surferID

surfer_name

surfer_seed

 

game_tbl

---------------

gameID

userID

 

I'm not sure where to go from here. I know I have those 3 entities (user, surfer, game). Any help is greatly appreciated and thanks a lot for reading about my project. If someone could maybe explain how I should set this up to best allow users to login and play a game, then be able to reset and play again, predicting a different winning scenario, it would be awesome. I just need the direction and method to implement this (sessions?). How should I set up the database and how should I temporarily track a users picks to give them the round 3 heat draw? I'm pretty new at this so Ive been reading as much as I can and searching for answers through online articles, I need help. THANK YOU!

[hanaleiroll]

I should tell you that I did make this game as a flat file database, but the problem IS it only works for one user at a time because I currently have fields in my table (round1, round2, round3) that get updated when the user makes their selection. For instance, if a user picks surfer (seed #5) to win his heat, it would put a '1' for round3 in my db table column round3. If a different user was to play, there would be a '1' for that guy even if they didnt pick him to win. My scripting currently looks for all surfers with a value of '1' for round 3 to display them in the round 3 heat draw (and round 2 for round 2 heat draw).  Here is the url to view what I've done...

Thanks again!

 

http://christiansurfmag.com/wctdraw/