need help designing tables for a fantasy basketball league manager
Dring the recent ncaa basketball tournament my buddies and I were talking about fantasy sports and we've got a concept that is a bit different from anything being done at the major websites I've checked (ESPN.com, sportsline.com, fantasysports.yahoo.com, etc). I told them that I'd try and code something up for next season once I got done with school this semester. Well, I had my last final exam yesterday afternoon so I've got all summer to try and make some progress on this. If it works, I think we'd have a lot of fun with it.
I've had limited experience with databases (my degree is in math) so I am hoping to get some help with how to set up the tables and the primary key / foreign key relationships, etc. Here is our idea:
At the beginning of the season, a user who want to participate begins by creating a group. Then each group creator invites their friends to come and join the group they created. Although users are primarily competing against other users in the same group, the web interface should also display overall point leaders regardless of which group they might be in.
To participate in the league, a user chooses ten players from a list of active ACC basketball players. The player’s position is not important. Each player can be chosen by multiple users. Each user must divide his players into five “active” players and five “bench” players. Users get points based on the real-game statistics of the active players on their roster. Points are awarded based on the following formula:
Users cannot change their ten-player rosters during the season. Users may, however, change a player’s status from active to bench and vice versa at any time. Changes to a player’s status become effective at midnight after the change.
When users log in, they should be able to see their current roster and a list of changes they have made to their rosters (and when they made them) in a "recent activity" section. So somehow I need to store these transactions in the database.
Finally, I need to keep statistics on all acc basketball games (even non league games). I will have very little difficulty importing these statistics from yahoo sports just by running a php script each night but I'm not sure how to set up a table (or tables) to hold player stats, game schedules. I'd like to track home vs. away performance because one of my theories is that certain players do better at home. So I would want that player on my roster but for road games I'd make him a bench player. How do I store home / away information in the tables?
If I can get the design figured out, then I'm confident in my ability to write the SQL queries from my .php pages to talk to the tables in the mysql databases. Any help would be appreciated. Thanks.