Re: 4 the FAQ: Are Commercial DBMS Truly Relational?

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Sun, 10 Oct 2004 20:54:37 -0400
Message-ID: <dklckc.mla.ln@mercury.downsfam.net>

Gene Wirchenko wrote:

> Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net> wrote:
> 
> [snip]
> 

>>But what if I mistype the last name when entering the second row? Now I
>>have "John Smith" and "John Smyth", with all other columns identical. The
>>rows are unique but wrong. So then we add a unique constraint on SSN, but
>>to every constraint we add I have a user who is going to put in a typo and
>>give a unique but incorrect answer.
>>
>>What body of theory do we depend upon to deal with this next level of
>>error?
>>
>>Practical experience seems to be that the Employee_ID column is the
>>answer. By requiring that single unique column, I can eventually trace
>>anomalies in the payroll system back to the presence of two "John Smith"
>>rows that have
>>different IDs. Great! Having discovered this human error after the fact,

> 
>      That single, unique column is also a constrained (PK).  What
> happens when a key for that column is entered wrong?  Why would it be
> any different for another key for another unique column, say SSN?
> 

The key fact about the Employee_ID column is that by definition it has no meaning, it is correct if and only if it is unique. It's only purpose is to be unique.

Practically, the use of such a meaningless column solves a lot of practical problems which I won't belabor here. This technique is ubiquitous.

Theoretically, this practice of introducing a meaningless unique column also just so happens to make my database "truly relational" at least insofar as all tables are now sets and not bags. A transaction id does the same for transaction tables.

My original question in this subthread is: what theory explains the benefits of the meaningless unique key? Do we claim that the RDM saved the day once we found a technique to use sets instead of bags? Or is the use of the key suggested by and supported by some other body of theory?

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Sun Oct 10 2004 - 19:54:37 CDT