I am familiar with relational databases, but I just don't know the BEST way to save information and not have it redundant. For example, I have these two teams in 2009 ( there would be more, but I want to try and keep this simple )
Midway Midgets 0 - 0
Highland Mavs 0 - 0
These are saved in a table ( table_teams ):
ID, Team, Mascot, Wins, Losses, Year
1, Midway, Midgets, 0, 0, 2009
2, Highland, Mavs, 0, 0, 2009
The next year, the Midgets change their mascot to Giants because certain small people were offended. I still want to be able to pull up that Midway was called the Midgets if I run a history. How would I go about saving this in the table? Would I just add:
3, Midway, Giants, 0, 0, 2010
Or what if an even worse extreme happened and they had to change their team name from Midway?
3, Mountain, Giants, 0, 0, 2010
How would I also go about getting a listing of these teams with such radical changes, similiar to this in the coming years?
2011 Mountain Giants 0-0
2010 Mountain Giants 6-0
2009 Midway Midgets 2-4
I admit I am slightly confused in how to go about this and probably can't convey to you exactly what I mean, but I have tried to be clear and simple in what I wish to do.
Another issue may be when using female names in a sports database. For example, a young lady in her freshman year of college is single. She gets married and the next year her last name changes. How do you work with an occurance like that when using a relational database?
I am just trying to analyze all the things that could and can happen. Another example: The Boxer Cassius Clay fighting for years under that name and then changing to Muhammad Ali.