数据库结构

Question

I am trying to create a fantasy football website. I'm trying to work out the table structure and I was looking for advice.

What I have so far:

usertable - > User Info    
playertable - > Player Info    
userleaguetable - > User League Info    
matchtable - > Match Info   
clubtable - > Club Info

Then the two tables that will be doing all the work:

scoringtable

Each week a players record will be added to the table, how many goals, how long he played, bookings, man of the match etc.

So that table will get pretty big: num_players * num_weeks

userteamtable

Each week the players on the users team will be added to the table, which player and which one was captain

So that table will (hopefully) get pretty big too: num_users * 11 * num_weeks

Why I was thinking of going this route with it is due to the fact that there will be a full week by week record of each users team, each players points etc.

So that's basically it, what I'm concerned about is table size, I mean if eventually there was 1000 users that would be 10000 rows added to the DB each week

Anyone have any suggestions for me??

Answer 1

Maybe the scoring table should have a row for each week with a unique identifier for the player. That way, in the scoring table you have 1 record per player with say 52 rows representing 52 weeks in the season. Each week you simply find the scoring record based on the unique identifier for the player and update that record with that weeks score.

This way, you're not adding a record every week for every player, you have 1 record per player for 1 season, it may look like this:

Player ID | Season ID | Week 1 | Week 2 | Week 3 | and so on..

For the next season, you add another record but change the season number. In 2 seasons you only have 2 records per player and so on. For example:

Bob the Player (ID 2450), Season 1, Week 1 score = 50, Week 2 score = 100, etc..

Player ID  |  Season ID  | Week 1 | Week 2 | Week 3 | and so on..
-------------------------------------------------------------------
2450-------|----- 1------|---50---|---100--|--150----

Hope this helps you see the rest of your database in a more efficient way. Good luck!

also wanted to mention, if you're storing multiple values per week just store them in this fashion:

HOw many goals: 5 How long he played: 15min Bookings: 500 Man of the match: 1 or 0

| Week 1      |
---------------
|5,15min,500,1|

Man of the match can simply be a 1 or 0 value, give the 1 value to the MVP and 0's to the rest, in the example above, this player was man of the match as the last value is a 1.

When your PHP reads in the Week 1 data, just explode it using the commas to create an array of the values for each week.