I'm creating fantasy football game and I have a question about how to design my database. I'm having difficulty designing a way to share players between leagues and still have it be normalized.
There is a table with all the possible football players called Players, a table that contains team information called Teams, a table that contains user information called Users, a table that contains league information and a table that keeps all the other tables id's called Rosters.
Basically there is a league that has a set amount of players, once the draft starts the players get set to a team and if they belong to a team they can no longer be drafted. The problem I'm having is, sharing these players with multiple leagues. Currently I have a boolean value set to the player if they are available or not. Which works until another league try to draft that player, because they have been set to unavailable.
My question is how should I make the player available/unavailable in the database so that different leagues have the same player selection ? Is there a better way of setting up this database ?
Rosters
CREATE TABLE Rosters (
RostersID INT NOT NULL AUTO_INCREMENT ,
LeagueID INT NOT,
TeamID INT NOT NULL ,
PlayerID INT NOT NULL ,
UserID INT NOT NULL ,
PRIMARY KEY (RostersID)
)
Leagues
CREATE TABLE Leagues (
LeagueID INT NOT NULL AUTO_INCREMENT ,
LeagueName VARCHAR(35) NOT NULL ,
PRIMARY KEY (LeagueID)
)
Teams
CREATE TABLE Teams (
TeamID INT NOT NULL AUTO_INCREMENT ,
TeamName VARCHAR(35) NOT NULL ,
StateCode CHAR(3) NOT NULL ,
Ranking INT NOT NULL DEFAULT '0',
PRIMARY KEY (TeamID)
)
Users
CREATE TABLE Users (
UserID INT NOT NULL AUTO_INCREMENT ,
UserName VARCHAR(45) NOT NULL ,
Email VARCHAR(55) NOT NULL ,
Password VARCHAR(45) NOT NULL ,
PRIMARY KEY (UserID)
)
Players
CREATE TABLE Players (
PlayerID INT NOT NULL AUTO_INCREMENT ,
LastName VARCHAR(50) NULL ,
FirstName VARCHAR(50) NULL ,
Postion VARCHAR NULL ,
Available BOOLEAN NOT NULL DEFAULT FALSE ,
PRIMARY KEY (PlayerID)
)
This is not really a database level question, this is a task for the application logic. What you can do on a database level is to place a multi column unique constraint in the roster table on leagueid, playerid fields. This would prevent your application to assign the same player to the same league twice. Get rid of the available field.
The available players for a league can be listed by left joining the players table on the roster table:
select p.playerid
from players p
left join rosters r on p.playerid=r.playerid and r.leagueid=...
where r.playerid is null