>It may not be required in normalization theory, but it's a great idea and your decision to "no longer strongly advocate meaningless IDs" is too hastily taken, IMHO (one thing that is required is that it not be a multipart field and your invoice number example may break that rule).

Sure, it's a great idea. But I no longer 'condemn' those who come with arguments as to why they want to use a meaningfull key in a certain design. That's what I mean when I write I no longer strongly advocate meaningless keys. And I do not want to be 'condemned' by others whenever I myself decide to use meaningful keys. It is the condemnation that is most of the times too hastily done.


>But take one of today's WTF (www.thedailywtf.com) entries: Turns out that a guy whose wife had twins called to put the kids on his insurance. The software the insurance company used tracked people by their last name, plan number, and month/year of birth. That seemed to be their primary key.

Their software should have shown two records in this case. If it turned out that their formula that creates a unique key created the same key for both twins, then it is their formula that has a flaw and needs an improvement. It is not necessarilly the design that is bad.


[snip]
>A surrogate key has no reason to change, ever (within the system).
[snip]
>In your invoice number example, what if the invoice date is changed?
[snip]

In a same vein, a meaningful key either should have no reason to change, ever. The design should guarantee that the invoice ID will never change after it has been created. In case the date appeared to be wrong, a counterbooking or deletion should be done and a NEW invoice should be made. In case the client wants to be able to later alter the date of that specific invoice, my advice would indeed be to not include the date in the invoice ID.


>>First, as I already tried to make clear, a surrogate, meaningless, key is NOT a prescription in normalization theory. It is no more than a trick. The absence of such a type of key is not a sign of bad design per se. A meaningfull key is permitted.
>>
>>As I already admitted, in case the company continues to infer the category from the employee ID, it is a sign of bad design. The atomicity rule is broken. But I now think that we all too often assume this too easilly. Another example may be clearer, but is essentially equal.
>>
>>In the Netherlands it is not illegal to include attributes like the date and/or the customer id in the invoice number. Let's say the invoice was written on 2007.01.25, the customer ID is 234, then the invoice ID might be 20070125.234.001. The design might describe a table with an invoiceID field, a customerID field and an InvoiceDate field. Many of us will be all too quick in saying that this table has redundancy! Where in fact it probably has no redundancy!
>>
>>The point is: The date and the customer ID are used only in the formula that CREATES the invoice ID. From that moment on the invoice ID is an attribute with NO relation to the date and customer ID, other than defined by the record structure. That is, no relation in the source code! It may well be that a user reads that ID and then directly thinks to know those attributes, but the developer is not, nowhere, allowed to depend on those parts of the invoice ID. The developer must use the other fields only. (And it may well be that the formula that creates the invoice ID changes over time.)
>>
>>The final test whether or not redundancy is real, requires a screening of the design and source code! If somewhere in that design or code a part of that invoice ID field is used to get the date or the customer ID, only then redundancy is proven.
>>
>>And this is what came to my mind, and what I wanted to share here.