Question about MySql Database table setup?

Question

I know the basics of php and mySql, and I want to create a database-driven website for a football pool. The pool works where each "user" picks four teams to win each week. My question is what is the best way to setup the tables? Obviously there will be one table for Users (with their name and ID and whatnot). But then should I have one separate table for each user's picks with fields like Week 1 Pick 1, Week 1 Pick 2, etc, etc? (there would be 68 picks totals, i.e. 17 weeks x 4).

Or is there someway that I can have one huge table with ALL users picks in it?

Any info would be appreciated?

The key is that i need to retrieve these picks each week and display the current "Standings" on the webpage.

Answer 1

you'll be better off putting all user picks in one table rather than separate tables for each user's picks.

you could create a Users table with

UserID

Name

Email

etc

then create another of Picks with

UserID (ID that matches the user id table)

Week #

Pick #

Picks

then when you query your tables you can use a join query to pull the userid & name from the users table and join it to the picks table (join on the ID field).  Then optionally restrict the query to show a particular week #, etc.

Answer 2

It sounds like you should have a table for users, a table for the football teams, and a table for the picks.  There are a lot of ways to do it, and obviously none of them are "wrong"-- it's just a question of how expandable they are, how fast they are, and how much work you feel like putting in.

I'd say something like:

users:

- user id

- user name

(and whatever else you know about your user-- encrypted password, favorite color, etc)

teams:

- team id

- team name

(other stuff about the teams, if necessary, like image icon, location, etc)

picks:

- week #

- user id

- team id

I don't think you actually need a "pick X" column, since the order of the picks probably isn't relevant.  If it is, then append on a "pick #" field.  And you could add additional information too, like "time of pick placement", etc, but that's probably unnecessary.  Anyway, you'd get multiple rows in the table for each pick they had.  So it might look like:

jeff - patriots - week 2

jeff - ravens - week 2

jeff - falcons - week 2

jeff - ravens - week 3

jeff - colts - week 3

(obviously, though, you'd want to use the user's numeric ID and the team's numeric ID, but hopefully that's clear enough)

You could also have a table showing the *actual* wins, so you could calculate the winners automatically as games were decided.  Hence, a table like:

winners:

- week #

- winning team id

- losing team id

Then you can compare the "winners" table to the "picks" table and figure out which userids were correct and which were wrong.

DaveE