Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Primary key opinion (Access 2002, Win 98 2nd E)

    I have a database that was original develped very poorly. I am getting ready to revise it. My question centers around the primary key. I have customers that have no real primary key, i.e. I don't use their social security number, there is no employee ID or any other major identifier. I thought about just using the AutoNumber, but that doesn't seem to be a good way to recall the customer. I though about a combination of part of the last and first names. But that can be problematic also I have had two people with the same first and last name. I don't always get telephone numbers and people also move.

    So I would appreciate everyone's comments and thoughts. Thank you. Fay

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Primary key opinion (Access 2002, Win 98 2nd E)

    We nearly always resort to an integer field that has no other real meaning than it is an ID number for the person/customer. Names just don't work, and even SSNs can have duplicates or may not be available. The downside is that you have to check for duplicates every time you add someone. Sorry there doesn't seem to be a better alternative - I'm interested to see how others view this issue.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Primary key opinion (Access 2002, Win 98 2nd E)

    You're mixing apples and oranges. The purpose of a primary key is to uniquely identify a record within a table. It is *not* to provide a mnemonic for users to find the record again. You do that with a unique key.

    I use autonumbers for my primary keys because they won't require changing when a name changes or a correction is made, and I guarantee you that those other candidates will require changing somewhere along the way. The autonumbers are there to uniquely identify the record, and for no other purpose.

    However, I also use unique keys that are user friendly. These are not primary keys, please note, merely unique keys that the user can relate to. This gives me the best of both worlds. When the client comes back and wants to change someone's social security number, it can be done without disrupting any of the data or relationships in the database, because that isn't the primary key and is not a foreign key in any other table. If they decide that their account numbers now have to be 9 digits instead of 7, no problem because account numbers are not the primary key.
    Charlotte

Posting Permissions

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