Re: How to ID a record
From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 08/30/04
- Next message: Mike: "VARCHAR"
- Previous message: Mike: "VARCHAR datatype"
- In reply to: Ray: "Re: How to ID a record"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 30 Aug 2004 11:51:37 -0500
> 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" <not@needed.com> wrote in message news:uv4OBGVjEHA.3456@TK2MSFTNGP12.phx.gbl... > 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. > >
- Next message: Mike: "VARCHAR"
- Previous message: Mike: "VARCHAR datatype"
- In reply to: Ray: "Re: How to ID a record"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
