Results 1 to 8 of 8

Thread: Name Change

  1. #1
    Join Date
    Aug 2004
    Location
    NY
    Posts
    113

    Unanswered: Name Change

    Hi all,

    I hope some one can give me some advice on this how to design a database, with name changes (last name)? Currently, in our database we track employee trainings. Recently, someone got married and they changed their last name. If we change the last name in the employee table, then any past trainings will reflect her new last name.

    How can I do it so that any new trainings will reflect new name and past trainings, her old name?

    I thought about making another field in the table but that would mean to add this field then into reports. Is this the correct way?

    Thanks
    ======================
    Levent
    Access 3

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Generally speaking, for User Tables, I have a Primary Key Field that stays static forever and ever. Then I have columns to reflect properties such as First Name, Last Name, DOB, etc. Doing it this way, you never have to worry about the Prim Key changing, even if you change the person's attributes (such as Last Name).
    Quote Originally Posted by Leventcos21
    I thought about making another field in the table but that would mean to add this field then into reports. Is this the correct way?
    You wouldn't have to add this column into your reports; the Prim Key should only be "behind the scenes", and just keep showing the pertinent data on the report.
    Me.Geek = True

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nick, how would your PK help you show the new name on new trainings, but keep the old name on old trainings?

    the answer, of course, is to have an additional column in the trainings table called surname_at_time_of_training, or simply surname

    easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2007
    Location
    Cologne, Germany
    Posts
    3
    Leventcos21,

    Is it imperitive you keep the old name anyway, surely if anybody ever has to go through her training records she would be referred to under her new name anyway

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another alternative, which would ensure that you won't one day have to ask "how do I record name changes if someone has changed their name after [insert anything you like here]?", is to have a name change history table. Something like person_id, surname, surname_valid_until. At least then you don't need to start recording the surname in various tables throughout your database every time you have some new temporal entity. Makes the SQL a bit more complex though of course
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2006
    Location
    Tucson, Arizona
    Posts
    19
    I agree with Nick that you need a primary key, whether it's an Autonumber key or another number. If you don't want to use a behind-the-scenes Autonumber PK, you could always assign the Social Security number as the primary key, since no two people have the same SSN. Then, no matter what happens with the person's last name (or first name for that matter -- Cassius Clay changed his name to Muhammad Ali) all the training records stay with the employee no matter what happens to his or her name.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blondeveloper
    ... since no two people have the same SSN.
    a common misconception

    i'm not american, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    In addition to the issue that the majority of the world's users don't live in the USA, due to privacy and identity theft issues, SSN is NOT a good key. In fact, no-one but HR and your payroll folks should ever have access to SSN's.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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