I have these two tables with team and mascot names.
Table: teams
Fields: id, team
1, Toronto
2, New York
..
342, Niagara
503, Los Angeles
Table: mascots
Fields: id, mascot
1, Argonauts
2, Monsters
..
124, Centipedes
I have this next table that collects all the information about games between each. For example, Toronto plays at Los Angeles.
Table: games
Fields: id, home_team, home_mascot, guest_team, guest_mascot, year
1, 503, 2, 1, 1, 2011
503 being Los Angeles and 2 being Monsters. Toronto and Argonauts both number 1 in both tables.
SELECT
g.*
FROM games g
INNER
JOIN
teams AS hteam
ON hteam.id = g.home_team
LEFT OUTER
JOIN
mascots AS hmascot
ON hmascot.id = g.home_mascot
INNER
JOIN
teams AS gteam
ON gteam.id = g.guest_team
LEFT OUTER
JOIN
mascots AS gmascot
ON gmascot.id = g.guest_mascot
WHERE g.year = 2011
The above query would go through the ‘games’ table and find/list all games in 2011.
But what would happen through the years if a team MOVED and changed its name. It would still be the same team, just different name.
2011 Toronto Argonauts
2012 Toronto Argonauts
2013 Niagara Argonauts
How in my ‘games’ table would I associate Toronto (ID 1) and Niagara (ID 342) being the same team?
I wouldn’t want to change the ID 1 in my ‘teams’ table and it look like the code below. I want people to see the history.
2011 Niagara Argonauts
2012 Niagara Argonauts
2013 Niagara Argonauts
If I have went about this the wrong way, any feedback will be appreciated. I am still trying to learn how to work with tables and normalize them. Am I on the right path or do I need to rethink it all?