sql server - season based database schema/design -

i have simple schema sports registration system till concept of season introduced:

a player registers part of team

a team registers part of club

a team plays in division

an organization creates divisions

this happens every season. need way keep track of of every season. best approach here? should introduce "season" entity , make many-to-many relationships between entities above applicable? or there way "archive" every season able pull season based data on fly (for reporting , not)?

this design allows teams move different division season season, while not having re-created every year (though doesn't allow things winnipeg jets moving phoenix , keeping old name 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 (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 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' seasons 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 want constraints or triggers prevent team belonging more 1 division in given year.

now you'll want denote roster 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 want tables schedules, standings, player stats etc... should start.