Re: Database design, Keys and some other things

Dr. Codd: "..Database users may cause the system to generate or delete
a surrogate, but they have no control over its value, nor is its value
ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and Codd,
E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result
that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates. Database users may cause the system to generate or delete
a surrogate, but they have no control over its value, nor is its value
ever displayed to them....." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture
more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

.

Relevant Pages

  • Re: Guidelines to a decent support of surrogate key implementation
    ... totally unrelated to the logical data model. ... A surrogate key is system generated to replace the actual key ... with a quote from Dr. Codd: ... Extending the database relational model to capture more meaning. ...
    (comp.databases.theory)
  • Re: [NOW ANSWER Aarons QUESTION CELKO]
    ... True surrogates are fine; ... surrogate keys Dr. Codd was refering to when he wrote: ... Codd in ACM TODS, pp 409-410) and Codd, E., Extending the ... database relational model to capture more meaning. ...
    (microsoft.public.sqlserver.programming)
  • Re: Negative Numbers in "Identity" or" Autonumber" fields
    ... WHAT IS A SURROGATE KEY? ... A quote from Dr. Codd: "..Database users may cause the system to ... "There are three difficulties in employing user-controlled keys as ...
    (comp.databases.theory)
  • Re: Negative Numbers in "Identity" or" Autonumber" fields
    ... WHAT IS A SURROGATE KEY? ... A quote from Dr. Codd: "..Database users may cause the system to ... "There are three difficulties in employing user-controlled keys as ...
    (comp.databases.theory)
  • Re: How should I generate a primary key?
    ... the external reality and verify them. ... be verified for syntax or check digits inside itself. ... A surrogate key is system generated to replace the actual key behind ... with a quote from Dr. Codd: "..Database users ...
    (comp.databases)