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). 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. "Having two people born in the same month and year on one plan would cause all claims to be booked against one of the two's budget, risking a premature budget exhaustion." So he had to enter one with the correct month and one with a month later. So a value that won't seem to change would actually change later if and when they fix this problem. And how does that affect all the other records built on this invalid birthdate? A surrogate key has no reason to change, ever (within the system). In your case, two systems were merged and, yes, since they may have had the same numbers, there can be some problems. We can't solve that, it's just part of integrating two systems. The only way to solve that would be to have a giant, world-wide database that stores everything. Then that problem wouldn't occur, but I don't think that solution is too practical. <g>

In your invoice number example, what if the invoice date is changed? It happens, but now anyone trying to rely on the invoice number for the date is automatically wrong (as you pointed out). Yes, your code may not pull the invoice date from the invoice number, but a person looking at a printout or who has only limited data might and it can lead to confusion. It's my feeling that - for the most part - database designers really prefer surrogate keys to meaningful keys. In my book, they are much better than meaningful keys.


>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.