mysql db design for US Fantasy Football

I belong to a fantasy football league that has members in different states. We would like to put our league on the web to make information more readily available than snail mail.

We have a unique scoring system so using an existing web based service is not an option.

I have worked w/mysql a little, but I am have trouble trying to design the basic structure of the db I want.

Here are the basics of the league:

30 team league w/2 conferences of 15 teams each. Each conference has 3 divisions (East, Central, West)

Each team has a roster of 21 players

Teams face off week to week through a predetermined schedule until week 15 of the NFL season. Week 15 is the Divisional playoffs (3 division winners and 1 wild card in each conf), 16 is the Conference championship (2 teams from each conf), and week 17 is the Fantasy Bowl where the 2 conference chaps face off.

What I would like to do on the web is show:

weekly results (W/L/T, Win %, Streak [# games W/L], Points Scored, Div Record, Conf Record)

weekly results

schedule by week

The trouble I am running into is each week will have games, each game will have a status (scheduled or played), each game will also have a home team and an away team, if played the game will have a winner aloser and scores.

When I try to figure out tables for this structure I keep going in circles.

Any advice??

Are you looking for contract help (I would be happy to help) or general tutorials on database design (in that case I would recommend SQL Unleashed published by SAMMS for its description of normalization and database design).

If you want to contact me, send email to einhverfr@hotmail.com and I would be happy to contract for you.

I appreciate the offer for the contract work, however this project is only for my fantasy league and obviously there is no income to generate a contract fee.

Again, I do thank you for the offer.

Or for that matter, any other good book on database design. That will get you thinking about how the best design will be handled.

I have been using some books, the problem is this db deviates from most of the standard examples in the books (ie address book or on line whatever store) so transferring the ideas gets confusing.

I have most of the db outlined, it just gets confusing when you start cross referencing teams as in a game with a home team and an away team.

Although thanks go out to Dennis Knowles for giving me a direction.

I currently run a multi-division, league system for LaserSquadNnemesis.
The DB structure looks somewhat like this
Season table - containing divisions (only required if divisions may change)
Division Table (containing player IDs)
Player Table (in your case, team table)
Game Table (contains week number, home player, away player, home player's score, etc)

Point totals, Standings, Streak, etc are a function of games played, rather than kept as distinct values in the DB.

Of course, calculating Points For/Against, Won/Lost/Drawn, Ratios, Points isn't simple ... but it can be done in one query :-

SELECT p.Nick AS PlayerName,
COUNT(*) as Played,
SUM(
IF(p.Pid=g.Pid1 AND g.Score1 > g.Score2, 1, 0) +
IF(p.Pid=g.Pid2 AND g.Score2>g.Score1,1,0)
) as Wins,
SUM(IF(g.Score1=g.Score2,1,0)) as Drawn,
SUM(
IF(p.Pid = g.Pid1 AND g.Score2 > g.Score1, 1, 0) +
IF(p.Pid = g.Pid2 AND g.Score1 > g.Score2, 1 , 0)
) as Lost,
SUM(IF(p.Pid = g.Pid1, g.Score1, g.Score2)) as P_For,
Sum(IF(p.Pid = g.Pid1, g.Score2, g.Score1)) as Against,
(SUM(IF(p.Pid = g.Pid1, g.Score1, g.Score2)) /
Sum(IF(p.Pid = g.Pid1, g.Score2, g.Score1))
) as Ratio,
SUM(
IF(p.Pid = g.Pid1 AND g.Score1 > g.Score2, 3, 0) +
IF(p.Pid = g.Pid2 AND g.Score2 > g.Score1, 3, 0) +
IF(Score1 = Score2, 1, 0)
) AS Points
FROM game g
LEFT JOIN player p
ON (p.Pid=g.Pid1 or p.Pid=g.Pid2)
WHERE (Score1 > 0 OR Score2 > 0)
GROUP BY p.Pid
ORDER BY Did, Points DESC, Ratio DESC;

If you would like the full "mysqldump -d" output, just let me know

Sorry it took so long to get back. I would like to get your mysql dump so I can see how you work your league.

cheesesteaks - you asked for it !

--
-- Table structure for table 'season'
--

CREATE TABLE season (
Sid int(3) NOT NULL auto_increment,
Current enum('SELECTED') default NULL,
PRIMARY KEY (Sid)
) TYPE=MyISAM;

CREATE TABLE division (
Did int(3) NOT NULL auto_increment,
Name varchar(255) default NULL,
Sid int(3) default NULL,
PRIMARY KEY (Did)
) TYPE=MyISAM;

--
-- Table structure for table 'game'
--

CREATE TABLE game (
Gid int(4) NOT NULL auto_increment,
Did int(2) default NULL,
Pid1 int(2) default NULL,
Pid2 int(2) default NULL,
Score1 int(3) default NULL,
Score2 int(3) default NULL,
ipaddress varchar(15) default NULL,
finish date default NULL,
Sid int(3) default NULL,
PRIMARY KEY (Gid)
) TYPE=MyISAM;

--
-- Table structure for table 'player'
--

CREATE TABLE player (
Pid int(3) NOT NULL auto_increment,
Name varchar(50) default NULL,
Email varchar(50) default NULL,
Nick varchar(50) default NULL,
Did int(3) default NULL,
Flag varchar(255) default NULL,
Icon varchar(255) default NULL,
PRIMARY KEY (Pid)
) TYPE=MyISAM;

Check out the displays of Fixtures and Standings here
It might not be perfect, but it works