CODEWITHSUNDEEP
sql-serverdatabase-design
Riz
Riz

Reputation: 6686

season based database schema/design

I have a simple schema for a sports registration system till the concept of a season is introduced:

A player registers as part of a Team

A Team registers as part of a Club

A team plays in a Division

An Organization creates Divisions

This happens every season. So I need a way to keep track of all of this every season. What would be the best approach here? Should I introduce a "Season" entity and make many-to-many relationships between all the entities above as applicable? Or is there a way to just "Archive" everything every season but also be able to pull all the season based data up on the fly (for reporting and what not)?

Upvotes: 1

Views: 1532

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

This design allows teams to move to a different division from season to season, while not having to be re-created every year (though it doesn't allow for things like the Winnipeg Jets moving to Phoenix and keeping their old name with their historical season stats).

CREATE TABLE dbo.Organizations
(
  OrgID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE
  -- , ... other columns
);

CREATE TABLE dbo.Divisions
(
  DivisionID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  OrgID INT NOT NULL FOREIGN KEY REFERENCES dbo.Organizations(OrgID)
  -- , ... other columns
  -- UNIQUE might be (Name) or (OrgID, Name)
);

CREATE TABLE dbo.Clubs
(
  ClubID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  -- , ... other columns
);

CREATE TABLE dbo.Teams
(
  TeamID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  ClubID INT NOT NULL FOREIGN KEY REFERENCES dbo.Clubs(ClubID)
  -- , ... other columns
);

Now to keep track of seasons:

CREATE TABLE dbo.Seasons
(
  SeasonID INT PRIMARY KEY,
  StartDate DATE NOT NULL,
  EndDate DATE,
  Name NVARCHAR(255) NOT NULL -- e.g. '1997-98' for seasons that cross Jan 1
  -- , ... other columns
);

CREATE TABLE dbo.SeasonTeams
(
  SeasonID INT FOREIGN KEY REFERENCES dbo.Seasons(SeasonID),
  DivisionID INT FOREIGN KEY REFERENCES dbo.Divisions(DivisionID),
  TeamID INT FOREIGN KEY REFERENCES dbo.Teams(TeamID),
  PRIMARY KEY (SeasonID, DivisionID, TeamID)
);

You probably want constraints or triggers to prevent a team from belonging to more than one division in any given year.

Now you'll also want to denote a roster for a given season / team.

CREATE TABLE dbo.Players
(
  PlayerID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  -- , ... other columns
);

CREATE TABLE dbo.TeamRoster
(
  PlayerID INT NOT NULL FOREIGN KEY REFERENCES dbo.Players(PlayerID),
  TeamID INT NOT NULL,
  SeasonID INT NOT NULL, 
  DivisionID INT NOT NULL,
  FOREIGN KEY (TeamID, SeasonID, DivisionID) REFERENCES
    dbo.SeasonTeams (TeamID, SeasonID, DivisionID)
);

You'll also want tables for schedules, standings, player stats etc... but that should be a start.

Upvotes: 2

Related Questions