Table Design - BAseball keeping historical teams

[Guest]

Hello All,

I have pretty much built my database to keep track of teams and what they
have won over the years. My dilema is keeping track of teams once they move
or rename themselves. I don't want to lose historical team names or past info
about them.

My current plan

http://www.buntonline.com/images/baseball.jpg

My current problems but not sure how to resolve them:

1) Franchise moves but I want to keep their entire history.

Montreal Expos become expansion team in 1967 and becomes the Washington
Nationals in 2005

2) Franchise does not move but renames themselves. I want to keep their
historical name for seasons past.

Anaheim Angels till 2004 and rename themselves to the Los Angeles Angles of
Anaheim

3) Franchise moves leagues and divisions but I want to know what league and
division they were in past seasons.

Milwaukee Brewers move from the AL East to the NL Central in 1997

Thanks for your time
Rob

 

[Douglas J. Steele]

One approach to handling changes over time is to change the one-to-many
relationship to a many-to-many relationship.

Rather than have LeagueAndDivisionsID as a foreign key in tblTeams,
introduce an intersection table between them, with LeagueAndDivisionsID,
TeamID and EffectiveDt as the primary key of that table. Include ExpiryDt in
the table as well. Have ExpiryDt be Null for the current row (to make
queries easier). I typically have ExpiryDt equal to the EffectiveDt of the
next row.

To handle name changes, you can have a parent-child relationship. Store
whatever's relevant to the "team" in the parent table, and store the names
in the child entries associated with that parent.

You can, of course, take this to extremes. tblLeaguesAndDivisions can change
over time, so you may want to put EffectiveDt and ExpiryDt there as well.