SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Relational Help

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

    Relational Help

    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 )

    Year: 2009
    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?

    2012 ...
    2011 Mountain Giants 0-0
    2010 Mountain Giants 6-0
    2009 Midway Midgets 2-4
    2008 ...

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    when the attributes of a thing change, what you want is to have a way of uniquely identifying that thing so that you can trace it through all its changes

    the best way to do this is to use a primary key that doesn't change

    you already did this in your teams example, by assigning a ID value to each team -- this value doesn't change even thought the other attributes do

    assigning an identifier (as that's what a primary key is) is a bit trickier with people, although a lot of applications simply use the social insurance number for this purpose (it has a small percentage of cases where it isn't a unique number, or has to be changed)

    as for the redundancy aspect, don't sweat it -- keep the attributes of the thing alongside the identifier of the thing, and you'll be okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What kind of key am I going to use on this?

    My table has:

    ID, First_Name, Last_Name
    1, Cassius, Clay
    2, Evander, Holyfield
    3, Mike, Tyson
    4, Mohammad, Ali

    Would I just not make ID Auto_Increment because Cassius Clay and Mohammad Ali are the same person, just he changed his name?

    Would I just add another key that would signify that they are the same person?

    ID, First_Name, Last_Name, PID
    1, Cassius, Clay, 1
    2, Evander, Holyfield, 2
    3, Mike, Tyson, 3
    4, Mohammad, Ali, 1

    The reason this is troubling me is should I run a query on Cassius Clay, I also want to see his fight results as Mohammad Ali, or vice versa.

    Ex:
    1986 Cassius Clay defeats some.boxer
    1987 Mohammad Ali defeats another.boxer

    then have it accumulate the records of both of these into one.. his wins-losses as Cassius Clay and his wins-losses as Mohammad Ali ( Because they are the SAME person, just with a name change )

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the same person, with a name change, should exist in the persons table only once

    multiple entries would be allowed in the persons_history table, but then you need an additional column for the primary key -- typically, start_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •