Normalizing Questions

[gregs]

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?

[felgall]

You wouldn't give it a new id when it changes name. You would just update the name on the id it is already using.

If you need to keep track of old names for a team then you need to add a from date to the primary key of the table containing the name.

[guido2004]

If each team can have only one mascot, and each mascot can have only one team, and the only data about the mascots you store in the 'mascots' table is the name, then it would be easier to have a mascot column in the 'teams' table, and get rid of the 'mascots' table.

[gk53]

I think you can add column relatesTo and store there original teamID in this case for any team you can find all records with the same relatesTo value and in order by teamId will give you history. Adding date when team has created may be beneficial too.

[gregs]

If each team can have only one mascot, and each mascot can have only one team, and the only data about the mascots you store in the 'mascots' table is the name, then it would be easier to have a mascot column in the 'teams' table, and get rid of the 'mascots' table.

The thing is that the mascot could change too and not just a team move or I may not understand what you mean.
I don't know. I am just going to have to keep thinking on it. I am probably making this more complex than it should be.

[samanime]

The first question to answer: Do you always need to keep track of every mascot and every team name, regardless of changes... or do you just need their current one.

If you always need to track them and need to know what they were, you'll need another field in your table, something like "original_team_id" which would be the ID of the original. If you just need the most up to date, then like felgall said, you'd just change the team name without adding a new entry to the database.

For the mascots, you need to decide if it's a one-to-one relationship. If every team always only has 1 mascot and any mascot can only belong to one team, then you can merge the two tables. If it's a one-to-many relationship (one team can have multiple mascots or one mascot can belong to multiple teams) then you should keep it split like you have it.

[oddz]

team:
- team_id (pk)
- history_id (fk teams history, uk)

team_history:
- history_id (pk)
- team_id (fk team)
- name
- mascot

game:
- game_id (pk)
- home_id (fk team history)
- quest_id (fk team history)
- year

[gregs]

That is the term I couldn't think of. One to Many. Yes, one mascot name may belong to many teams, so I would have to leave it as its own table.

In regards to your first question, I want to keep track of each one for a HISTORY purpose. If the team is fifty years old, have it so you can see all of its variations, from team name changes to mascot name changes.

2009 Oak Ridge Timbers <- Their first year for example. Newly made!
2010 Oak Ridge Loggers <- They decided to change their mascot name from Timbers to Loggers.
2011 Oak Ridge Heights Loggers <- It's the same team, but they added 'Heights' to it. See. The history keeps up with all changes

I think I will have to have an Original Team ID number also as people have mentioned. I just wonder how I would carry it over from one year to the next. For example, when Oak Ridge is first created in 2009, how would I set orig_team_id to the new autoincrement number, it being added to the table?

[DaveMaxwell]

I would do something more along the lines of this:

Franchise
FranchiseID
FranchiseName
FranchiseDate

Team
FranchiseID - FK to Franchise
TeamID
TeamName
StartDate
EndDate

Mascot
MascotID - PK
TeamID - FK to Team
MascotName
StartDate
EndDate

Game
GameID - PK
HomeTeamID - FK to Team (TeamID)
AwayTeamID - FK to Team (TeamID)
GameDate

What that will do will allow you to channel all the information for a a franchise, and allow you to handle the various incarnations of the teams, PLUS the incarnations of the mascots (which are separate to the game unless you're looking to be real anal and track which mascot(s) were at which game). Teams can have more than one mascot, which is why it's in a child table. This is NOT to share multiple mascots amongst teams - they may look the same, but they're different mascots and should be treated as such.

[gregs]

Mr Dave.

I have no idea how to incorporate my ideas into your format.

Franchise?

Multiple Mascots per team?

I guess the Franchise part is what really messes me up and how it works with the other tables.

Thank you for replying though.. everyone who has.

[DaveMaxwell]

Mr Dave.

I have no idea how to incorporate my ideas into your format.

Let's see if I can answer your questions....

Franchise?

Actually, this handles almost exactly what you want to see. A franchise is the singular business entity that the team operates out of. A franchise is essentially a contract (may not be formal) between a league and an organization that wants to play in that league. Establishing a franchise setup allows you to track the changes which occur while still establishing a cohesive timeline.

An example:

In the National Football League (professional American Football), there was a team called the Houston Oilers. They existed as the Houston Oilers from 1960-1996, at which time they moved to Nashville TN and became the Tennessee Oilers. They played under that name for the 1997-1998 and 1998-1999 seasons, at which time they changed their name again to the Tennessee Titans, which they've played as ever since. Same owners, same franchise, various team names. So any records established under the Houston Oilers team will still hold for the Tennesse Titans (for example, Warren Moon (Houston Oilers) holds the career passing record, while Eddie George (who played as a Houston Oiler, a Tenessee Oiler and a Tennessee Titan) holds the career rushing record.

Multiple Mascots per team?

Yup. The minor league baseball team in my area has two mascots they currently use on a regular basis, Grrounder and Riverside Rascal.

I guess the Franchise part is what really messes me up and how it works with the other tables.

Hopefully the answer I provided above cleared it up?

[gregs]

Mr Dave.

Sorry for such a long delay in replying, but I just can't for the life of me figure out how to use your Franchise model.

Below is how I would want to pull up and view a HISTORY of a school. You notice in 2008 they were the Timbers and in 2009 they changed to Lumberjacks. I think I can handle that part. My problem is how to associate that Oak Ridge from 2008-2010 is also the 2011 Oak Ridge Central team. They might even just drop Oak Ridge completely in 2012 and go by just Central.

Year  Team
----  ----------------------------
2008  Oak Ridge Timbers
2009  Oak Ridge Lumberjacks
2010  Oak Ridge Lumberjacks
2011  Oak Ridge Central Lumberjacks
2012  Central Lumberjacks

You say to have a Franchise table. Okay.
FranchiseID 1
FranchiseNAME ?? <- What goes here? How does it point to both Oak Ridge, Oak Ridge Central and Central?

Am I just too simple minded to understand it? I am really, really stressed out about this.

[DaveMaxwell]

Mr Dave.

Sorry for such a long delay in replying, but I just can't for the life of me figure out how to use your Franchise model.

Below is how I would want to pull up and view a HISTORY of a school. You notice in 2008 they were the Timbers and in 2009 they changed to Lumberjacks. I think I can handle that part. My problem is how to associate that Oak Ridge from 2008-2010 is also the 2011 Oak Ridge Central team. They might even just drop Oak Ridge completely in 2012 and go by just Central.

Since I didn't know you were talking about schools (your example above used city names and a CFL team), then the table franchise would be better off being named Schools.

Here's how the data would lay out:

Table:  School
    SchoolID        SchoolName       
    -----------     -----------------------------
    1               Oak Ridge High
    2               Maple Line Senior High School
Table: Team
    TeamID  TeamName                        FranchiseID     StartDate   EndDate
    ------  -----------------------------   -----------     ---------   ----------
    1       Oak Ridge Timbers               1               1/1/2008    12/31/2008
    2       Oak Ridge Lumberjacks           1               1/1/2009    12/31/2010
    3       Oak Ridge Central Lumberjacks   1               1/1/2011    12/31/2011
    4       Central Lumberjacks             1               1/1/2012    12/31/2012
    5       Maple Line RidgeRunners         2               1/1/2007    12/31/2009
    6       Maple Line Bats                 2               1/1/2010    12/31/2012
    
Team: Mascot
    MascotID    TeamID  MascotName      StartDate   EndDate
    --------    ------  --------------- ---------   ----------
    1           1       Tommy Tree      1/1/2008    5/31/2008
    2           1       Tommy Timber    6/1/2008    12/31/2008
    3           1       Jack Lumberjack 1/1/2009    12/31/2009
    4           1       Fred Flannel    1/1/2009    12/31/2010
    5           2       Ruby Runner     1/1/2007    12/31/2009
    6           2       Rex Maple       1/1/2010    12/31/2012

[gregs]

So, if I wanted to view just the 2012 teams, I would use

SELECT * FROM team where year = 2012

or

SELECT * FROM team where FranchiseID = 1

I have never worked with a linking table like School.
How would I target a team like #5, Maple Line Ridgerunners, if I wanted to see the 2008 teams?

Which table would I be using in my GAMES table when I have it FK to a team for that year?

[kuangren]

You just updated ID, it has used the name. If you need to keep a team of the old name, then you need to add a date contains a list of names of the primary key. Because each team can have only a mascot, each mascot can have only one team, and your "mascot" list is the name of the store to the only data mascot,

[DaveMaxwell]

If you're going to work with whole years, then you could change the start and end dates from full dates to a number based field, but I would personally leave it date based as it allows more flexibility.

As for your questions:

1) To view the 2012 teams, you would do: SELECT * FROM Team WHERE startdate <= '1/1/2012' and enddate >= '12/31/2012'
2) To view the 2008 team, you would do: SELECT * FROM Team WHERE startdate <= '1/1/2008' and enddate >= '12/31/2008'

Notice on the two queries that the <= and >= are probably reversed what you would expect (when I first did it I reversed it until I caught it) This is setup this way because you want your date to fall within the date range on the records.

Now if you were looking for all of the teams from Maple Line Senior High School (i.e. you don't know the team names) for 2008, then the query would be (note: I noticed an error in my previous posting - FranchiseID on Team should be SchoolID):

SELECT s.SchoolName
      , t.TeamName
      , t.startDate
      , t.endDate
   FROM School s
   JOIN Team t on s.SchoolID = t.SchoolID
  WHERE SchoolName = 'Maple Line Senior High School'

[gregs]

Would I have to manually update certain SCHOOL and TEAM table fields each year(season)?

For example, 2013 comes along. Nothing changed team wise. How would I go about changing the EndDate for each active team in the team table?

What if a new school was made. Would I manually put the school in the SCHOOL table then add it also to the TEAM table?

Schools also change CONFERENCES every few years due to enrollment. Which table would be best to place a Conference field.

As you can see, I am just analyzing this to death.

[DaveMaxwell]

Would I have to manually update certain SCHOOL and TEAM table fields each year(season)?

For example, 2013 comes along. Nothing changed team wise. How would I go about changing the EndDate for each active team in the team table?

You would just run a UPDATE Team SET EndDate = '12/31/2013' WHERE EndDate = '12/31/2012'

What if a new school was made. Would I manually put the school in the SCHOOL table then add it also to the TEAM table?

Exactly right...

Schools also change CONFERENCES every few years due to enrollment. Which table would be best to place a Conference field.

I would put it onto the Team table. The only reason I'd do that is because that allows you to track the conference they have been in.

As you can see, I am just analyzing this to death.

I've been accused of that many a time - I'd rather build it with growing room than have to retool the entire structure when there's live data involved.