Re: candidate keys in abstract parent relations


"Roy Hann" <specially@xxxxxxxxxxxxxxxxxxxxx> wrote

> When we record a driver's licence number or an employee number, and name,
> all we are saying is that we believe that number was assigned to a person
> who will claim to have that name. Most of the time that also happens to
> work as identification for the person, but IMO that is an accidental
> side-effect of most people being honest. But I have a colleague who works
> on a criminal justice system, and they tie themselves in fantastic knots
> because they refuse to understand this.

It's a side effect of most people being honest, but by no means accidental.
We have been keeping records since the dawn of recorded history (sic), and
we have always relied on records to contain "truth". We use persistent,
machine readable storage today, instead of clay tablets or papyrus scrolls,
but the idea is the same: we record what we believe to be true, and we rely
on records to be true.

Most information systems have some means of detecting error or deceit, but
they are limited. For example, a driver's license usually has a photo on
it.

The use of a driver's license number to identify a person is actually the
use of a foreign key as if it were a primary key.
The license number is an attribute of the license, not of the person. We are
relying on the relationship between persons and drivers licenses to get away
with this philosophical slieght of hand.

>
> I have never met a database designer yet, including myself, who doesn't
> struggle to remember that the database only has to assert what we are
told,
> NOT what is objectively true. It doesn't "matter" if the database
contains
> lies as long as it (and the application) doesn't invent or derive new
lies.
> (Obviously there is a burden on the person entering the data to be
diligent
> about establishing the truth as best they can before they enter it, but
> that's not a database design problem. Also obviously, it is nice to test
> the internal consistency of the assertions we record, if we can. And just
> as obviously there has to be a way to remove lies and all their false
> derivations when they are discovered.)
>
Two points:

Your post reminds me of the "database contents as opinion" concept.

Second, there are two ways in which value can be measured in the
information industry: content and conveyance.

Considering the database as pure conveyer, what you say above is true. But
if the database is valued for content, not merely conveyance, then the above
comment is merely finger pointing.

Consider a dictionary, as a database of words and definitions. If a word is
mispelled in the dictionary, the guy who runs the printing press can claim
that it's not his fault. But somebody in the production of the dictionary
is responsible.


> I am in danger of turning the conversation full-circle here by remarking
on
> how surrogate keys are helpful, so I will stop now. (The problem IMO is
how
> to discourage using them, not the opposite!)

Surrogate keys are a hammer. Not every design case is a nail.



.

Relevant Pages

  • Re: Append query to multiple tables
    ... the database to give the actual table and field names). ... > values for the foreign key fields in the License table?" ... > you are adding the new record (appending the record). ... > primary key value that is created when the record is being added ...
    (microsoft.public.access.externaldata)
  • Re: Locked out of Access database
    ... There were probably some documents before the original invoice. ... states nothing about my purchasing a license ... agreement, in writing, invoiced or verbally. ... About 4 months ago, when logging in to my database, a message popped ...
    (comp.databases.ms-access)
  • Re: Locked out of Access database
    ... There were probably some documents before the original invoice. ... states nothing about my purchasing a license ... agreement, in writing, invoiced or verbally. ... About 4 months ago, when logging in to my database, a message popped ...
    (comp.databases.ms-access)
  • Re: Locked out of Access database
    ... states nothing about my purchasing a license ... I never paid for a license because a license was never part of our ... agreement, in writing, invoiced or verbally. ... About 4 months ago, when logging in to my database, a message popped ...
    (comp.databases.ms-access)
  • Re: Locked out of Access database
    ... There were probably some documents before the original invoice. ... states nothing about my purchasing a license ... agreement, in writing, invoiced or verbally. ... About 4 months ago, when logging in to my database, a message popped ...
    (comp.databases.ms-access)