Re: A real world example

JOG wrote:

Just as it is good to describe a person by something that will remain
consistent (for example telling someone that they will recognise 'john'
by his 'green jumper' is not particularly useful if he does actually
change his clothes once in a while), so in database design it is vital
to pick a good candidate key. Desirable properties are:

1) very likely to maintain uniqueness over relation values.
2) very unlikely to change between relation values.

The RM does not require that anything is immutable, but clearly,
finding a key that tends to immutability is desirable for (2).

It appears to me reading the posts in this thread, that everyone is
agreed on these points, so I would like to suitably bang all your heads
together, as in some instances you are all just agreeing loudly, just
with the vociferous bluster of miscommunication.

I disagree with your analysis.

A self-aggrandizing ignorant showed up and posted nonsense. In fact, he continues to post nonsense. However, he now has you trained to re-define your existing vocabulary to turn his nonsense into something not only sensible but wholly unremarkable.

Design criteria for choice of candidate keys include: familiarity, irreducibility, simplicity and stability.

See http://www.dbdebunk.com/page/page/622344.htm


Given that all are agreed that surrogates are useful

I disagree that the concept of surrogate vs. natural is useful. A natural key is merely a familiar surrogate. The self-aggrandizing ignorant now has you redefining terms to make "surrogate" synonymous with "stable" and to make "natural" synonymous with "unstable".

By accepting his misuse of vocabulary, you encourage and legitimize the illegitimate while you interfere with communication and comprehension. Is it your goal to understand theory or to market the services of ignorants at the expense of their potential clients and other stakeholders?


, this leaves the
_only_ point that seem to be in contention:

"Should a surrogate be made hidden from the user"

I disagree that the above is the only point that remains in contention, and I would further note that the issue does not remain in contention among any who are reasonably informed and intelligent.

Familiarity is generally as important a design criterion as stability. In specific contexts, one or the other may have greater pragmatic repercussions. Hiding values not only precludes familiarity but violates the Information Principle with all that that implies.


I'd contend no, absolutely not. Surrogates are instigated by real world
difficulties in distinguishing items even if they are generated by a
DBMS (in fact it does not matter a jot what generates them logically).
Hence if they are hidden, it is a clear violation of the information
principle, which is vital for the real world identification that
precipitated the need for them in the first place.

All identifiers--even in 'the real world'--are surrogates. Every last one of them. What then is the use of the term? Twenty years ago there was some discussion of surrogate keys vs. natural keys, but further reflection reveals that natural keys are nothing more or less than familiar surrogates.

The term 'intelligent key' at least defines a proper subset of keys. The drawback of an intelligent key is increased instability. Sometimes, though, intelligent keys offer mnemonic or verification advantages related to familiarity. Whether to use an intelligent key then becomes a pragmatic tradeoff between stability and familiarity.

There really is little more to discuss about natural keys vs. surrogates except that self-aggrandizing ignorants periodically appropriate the terms in the nonsense they spout. The more you try to make sense of the nonsense the more you legitimize the self-aggrandizing ignorants without any further benefit to anyone.

If you are going to engage the self-aggrandizing ignorants, please, do better at calling them on their bullshit.
.

Relevant Pages

  • Re: A real world example
    ... I disagree that the concept of surrogate vs. natural is useful. ... Familiarity is generally as important a design criterion as stability. ... The term 'intelligent key' at least defines a proper subset of keys. ... except that self-aggrandizing ignorants periodically appropriate the terms ...
    (comp.databases.theory)
  • Re: Whats the best practice for primary keys?
    ... The idea behind surrogates is introduced not by Codd, but by Hall, Owlett & ... the surrogate values) and an e-relation (the corresponding relation with the ... The need for logical surrogates in relational databases is genuine; ... compound keys have missing values in part of the referencing columns. ...
    (microsoft.public.sqlserver.programming)
  • Re: What are the design criteria for primary keys?
    ... specific design criteria. ... Ever since I first saw it mentioned I've especially liked 'familiarity' ... candidate keys, the ones that aren't primary being very useful ... defining quasi metaphysical subjective criteria such as ...
    (comp.databases.theory)
  • Re: What are the design criteria for primary keys?
    ... specific design criteria. ... Ever since I first saw it mentioned I've especially liked 'familiarity' ... Celko wants everybody to use published keys, ... defining quasi metaphysical subjective criteria such as ...
    (comp.databases.theory)
  • Re: Set Based Row Duplication
    ... Surrogate Keys are keys in the usual Relational sense but have the following specific properties: ... Their values serve solely as surrogate for the entities they stand for ...... ... When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted. ... A quote from Dr. Codd: "..Database users may cause the system to ...
    (microsoft.public.sqlserver.programming)