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

No condemnation, but we're in the vein of "best practices", so I'm trying to talk about the rules. I have been known to break a few <g>, because I believe there are times that the purity of perfect normalization is more trouble than it's worth.

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

Yes, their design was faulty. By that I mean the portion of the design that said the three fields would be unique. I have no reason to doubt what was related via the website, but when you think about it, it's really extra stupid. It said the key was last name, plan number, and month/year of birth. How could any person with one-tenth of a brain not realize that two people in the same family, under the same plan, could be born in the same month? In fact, a design like that is so foreign to my thinking that when I first read it I did not even realize they meant month/year. I thought it was month/day/year. I guess my programmer subconscious assumed that. At least it would be not quite so dumb as the glaringly dumb month/year design. It was only later, when I relayed this to you that I realized how truly idiotic that design is.

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

I'd have to disagree there. Because the meaningful key contains data (whether it's actually used that way by the system or not), there is a chance it can change. I don't want to delete an entire invoice and recreate it just to change an invoice date! With a surrogate key, that won't be a problem. Just change the date and go have your cup of coffee. I'm not saying meaningful keys are *never* justified. It's just that I avoid them as much as possible.

Hey, good discussion.