Re: Strategy for combining lists

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The point about having a natural key is clear. Each company in a vendor
database is distinct from each other company by virtue of easily recognized
attributes such as company name (and maybe address, although that need has
not arisen so far). At the same time, quite a few companies have changed
their names, become affiliates of other companies, and so forth. However,
all records associated with the old company name are still associated with
the new one without the need to do cascade updates or other machinations.
I never meant to suggest that I regard a surrogate PK (or a "natural"
numeric one such as EmployeeID) as a substitute for a unique record, but
merely as a way the database can identify that record without my
intervention.

"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in message
news:1174061180.311746.114130@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 16, 2:08 pm, "tina" <nos...@xxxxxxxxxxx> wrote:
the
clearest explanation, that i have ever read, of the purpose of a primary
key
in a table is athttp://www.dbpd.com/vault/9805xtra.htm

i know it really clarified my thinking; and it validated my choice of
surrogate primary keys for most situations.

I hope you haven't missed that article's point that in order to have a
surrogate ('physical key') you must first have a logical key: "The
logical unique key distinguishes entity occurrences from others to
avoid duplicates before entry into the database. That is why this key
is typically composed of attributes and relationships that the
business can recognize as distinguishing occurrences logically from
others. The primary key, however, is a physical design element that
uniquely identifies rows after they are in the database."

BruceM has told us he has a real life natural logical key of 'company
identifier' and trainer name'. He needs to constrain this natural key
in the DBMS to prevent duplicates in the DBMS. If a table's only
unique constraint is a PRIMARY KEY designation on an autonumber column
then there is nothing to prevent duplicates e.g.

INSERT INTO ExternalTrainers (company_duns, trainer_name)
VALUES ('12345678900', 'Joe Jones')
;
INSERT INTO ExternalTrainers (company_duns, trainer_name)
VALUES ('12345678900', 'Joe Jones')
;
INSERT INTO ExternalTrainers (company_duns, trainer_name)
VALUES ('12345678900', 'Joe Jones')
;

If there was an autonumber of the above table it would not be
preventing those duplicates.

Sure, add a surrogate to the table if you think it adds value (more
efficient table joins etc) but before you do ensure you've uniquely
constrained your logical key.

Jamie.

--




.

Relevant Pages

  • Re: Natural keys vs Aritficial Keys
    ... there's some real good reason to go with an artificial key. ... the database, I'm treating that key as "natural" in the context of the ... invitation to degradation of data integrity. ... If a natural key rigidly designates or describes ...
    (comp.databases.theory)
  • Re: Natural keys vs Aritficial Keys
    ... there's some real good reason to go with an artificial key. ... database, even though one might argue that it's "artificial" in some other ... invitation to degradation of data integrity. ... If a natural key rigidly designates or describes something--that ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... a natural key and belongs in the database - regardless of whether it ... Otherwise, it is garbage (artificial, surrogate, ... It's generated by some process in the application layer. ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... a natural key and belongs in the database - regardless of whether it ... Otherwise, it is garbage (artificial, surrogate, ... It's generated by some process in the application layer. ...
    (comp.databases.theory)