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??
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:
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.