Re: identity columns

>> I disagree. The entity type and the base table derived from it
should by named as an abstract representation of its instances, thus a singular noun, such as Employee.

I noticed that you said "representation of its instances" and not "set" or "class" and that might be important. OO people think of instances rather than sets. I would say that a forest contains trees; you would say that there is no such construct as a forest, but only instances of trees. This goes back to the concepts that Cantor introduced with set theory.

>> Consult Date or another standard textbook. <<

Date uses both plural and singular names. I am re-writing mine to favor collective nouns. A quick look at the stuff in the SQL books on my shelf shows more plurals in later books. Try the INCITS L8 Metadata Standards Committee data element naming rules. Scalars are singular in their rules.

>> I disagree. Your argument shows exactly why a key should not carry
information: what if the company wants to keep a record of fired employees.
A record in a table should be "a fact"; that is not the case if the person
no longer holds the email-address. A key should be immutable. <<

A data warehouse is a different problem. If you REALLY want an immutable key for a person, then we use fingerprints and DNA -- the prison system again. Most employers are not that concerned about the location of ex-employees; most of them are not crooks <g>.

But a company assigned email address can be a unique string that is checked to see that it is issued only once. AOL, Yahoo, et al seem to be able to do this with lots of people by adding a number after a name the customer can remember. They have a high turn over rate as well.

>> As an example you might think of records of invoices, which may
have a
natural candidate key combined of foreign keys to Customer and Order. Nevertheless, a running integer is often used as a primary key, eg. to document (for the auditor) that no sale is forgotten. <<

If my business rules say that an invoice has one customer number and one order number, then I want to have a UNIQUE(customer-nbr, order_nbr) constraint in my schema.

The advantage of the separate invoice number is that you can relate it to a physical piece of paper or to multiple orders on the same invoice or multiple invoices for a single order. The customer has his own purchase order number on the invoice, but it is possile that two customers might duplicate a purchase order number. Received on Sat Jan 12 2002 - 23:45:33 CET