SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Normalizing Questions

    I have these two tables with team and mascot names.

    Code:
    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.

    Code:
    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.

    Code:
    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.

    Code:
    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.

    Code:
    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?

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,804
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)

    Arrow

    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by gregs View Post
    Mr Dave.

    I have no idea how to incorporate my ideas into your format.
    Let's see if I can answer your questions....

    Quote Originally Posted by gregs View Post
    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.

    Quote Originally Posted by gregs View Post
    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.

    Quote Originally Posted by gregs View Post
    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?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by gregs View Post
    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:

    Code:
    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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  14. #14
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So, if I wanted to view just the 2012 teams, I would use
    Code:
    SELECT * FROM team where year = 2012
    or
    Code:
    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?

  15. #15
    Non-Member
    Join Date
    Oct 2011
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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):

    Code:
    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'
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  17. #17
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by gregs View Post
    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'

    Quote Originally Posted by gregs View Post
    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...

    Quote Originally Posted by gregs View Post
    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.

    Quote Originally Posted by gregs View Post
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •