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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote