Hello,

I need to create a database and I would appreciate a bit of guidance so that I can set it up the best possible way from the start.

The purpose of the database will be to catalog predictions and then analyze those predictions after the outcome is known. The predictions in this case are NFL football stats.

Each week, 4 people ('predictors') will try to predict how many yards and touchdowns several NFL players will have. The data set for 1 week would look something like this:

Week | Player | Predictor | Yds Predicted | Tds Predicted | Yds Actual | Tds Actual
1 | Peterson | Jim | 100 | 1 | unknown | unknown
1 | Peterson | Bill | 156 | 2 | unknown | unknown
1 | Peterson | Bob | 60 | 0 | unknown | unknown
1 | Johnson | Jim | 167 | 1 | unknown | unknown
1 | Johnson | Bill | 188 | 0 | unknown | unknown
1 | Johnson | Bob | 143 | 1 | unknown | unknown

That doesn't look very good - so for a better look:

Data Set - Google Spreadsheet Representation
(I used a google docs spreadsheet because you can't draw a table in a vbulletin forum to my knowledge)

Each week there would be a new similar data set, and the outcome from the previous week will be known. I will want to query the data sets to find out whose predictions were the most accurate for each week.

I guess I am struggling with how setup the tables and overall database design. What would be the ideal way to set this up? I guess I really don’t know what should get its own table and what shouldn’t.

Should I keep each season in one giant table that has Week, Predictor, Player Name, Yards Predicted, TDs Predicted, Yards Actual, TDs Actual as the fields?

-OR-

Should I:
Have a separate table for NFL Players with columns PlayerID, Player name?
Have a separate table for the predictions of each Week (or maybe Season)?
Have a separate table for each predictor (person who is making the predictions)?

This seems like such a simple thing to think through, but I just can’t seem to decide what makes the most sense on my own. I want to avoid getting the whole thing set up and then realizing I should have done it another way .

Thanks a lot for reading this and for any input!

my advice is to forget about "the ideal way to set this up" and use a single table

in fact, you can likely do it in a spreadsheet, just like the one you posted on google

what was your reason for not wanting to do it with a spreadsheet?

Well I was planning to accumulate several years of data, but maybe the best thing to do is use a spreadsheet until the year is finished and then put it into the database.

I also want to have people eventually input their picks on a web form that will go into a MySQL database.

For 4 people a spreadsheet would work but I think it would get too big with a lot of people playing.

Thanks a lot for the suggestion though, believe it or not it makes me feel a lot better about not being able to figure out how I should set it up given that it's pretty simple... I am probably making it overcomplicated for no good reason.

putting completed years into a database is worth doing only if you expect to obtain valuable statistical information from it using complex retrieval queries

otherwise, i wouldn't bother

by the way, i run an nfl picks pool on my site, with approx 60 players making 16 picks every week of the nfl season -- way too many to maintain in a spreadsheet comfortably