How to ID a record

I'm voting for the surrogate and I think this seals it for me in no
particular order...

Ray,
Before you make your vote final, let me weigh in on the same side as Tim
Ferguson, with a few additional thoughts.

1. The surrogate is a concrete form of ID'ing records.
I don't have to stress about the maybe's... will a vehicle being
re-regged,
or trying to alter a primary key later and all the other probabilities.

In reality a surrogate key doesn't really identify a record except so far as
the database engine is concerned. All will agree that a surrogate key is a
meaningless value and, while each value of the surrogate key is unique in a
table, it doesn't identify anything about the rest of the record in a real
world. As Tim mentioned, you can enter multiple duplicate records if a
surrogate key is the only thing you are relying on. Certainly, a surrogate
key is a good choice for creating relationships between tables, but it is a
very poor choice for eliminating redundant data, which is one of the main
things that a primary key is supposed to do.


2. The surrogate is tried and tested everytime.
I don't have to consider every field or field groups as a candidate for
uniqueness and then go through all the what if's?

Tim mentioned some of the implementation flaws with surrogate keys, but let
me re-emphasize some other issues. If you use a single field surrogate key
as your sole primary key and do not place a unique index on other fields in
the table, then you are bound to have duplicates. So, even if you do use a
surrogate, you still need to be concerned with a natural candidate key for
uniqueness.


3. Coding convention.
If I see employess.id or vehicle.id I know what it is everytime without
consideration or second guessing.

Actually, there are no guarantees that the names of fields will be unique,
but referencing a single field in code is one reason for using a surrogate
key, in some instances.

Finally, as with Tim I think surrogate keys are a valuable tool
(particularly for relating tables), but they should be used only when
necessary and should not be considered as a fool proof tool for database
design.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Ray" wrote in message
...
Thank you for your comments John, Jeff and Ken.

I'm voting for the surrogate and I think this seals it for me in no
particular order...

1. The surrogate is a concrete form of ID'ing records.
I don't have to stress about the maybe's... will a vehicle being
re-regged,
or trying to alter a primary key later and all the other probabilities.

2. The surrogate is tried and tested everytime.
I don't have to consider every field or field groups as a candidate for
uniqueness and then go through all the what if's?

3. Coding convention.
If I see employess.id or vehicle.id I know what it is everytime without
consideration or second guessing.

4. It's simple to use!


Maybe coding convention is my biggest pull out of these 4.

I'm glad I raised the question and I'm grateful as always for you guys
giving me a help :-)

Ray.

"Fred Boer" wrote in
:

I wouldn't mind having these things explained to me by a kindly,
patient granny...

Must be thinking about someone else's granny... g

All the best


Tim F