Hi Russell

>>>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.
>>
>>What I'm trying to make clear here, is that using meaningful keys is not 'breaking the rules' and using meaningless keys is not a sign of purity. Perhaps that is also true of some of your other 'breaking' practices?!
>
>I don't know what you mean by that last sentence. It sounds kind of snide. I didn't mean to imply that a surrogate key was in the rules, but it is one of the things you can do - and is often done with great success - to achieve the requirements for an ideal primary key. I just never liked PKs that span multiple fields and use actual data. Though I have used them, in general, I try to avoid them.

As the real SQL gurus advise, a primary key is supposed to be composed of multiple fields. I believe that is the index, of course, not that there be a single field that holds the concatenated value. A surrogate is supposed to be the exception.

I've been using surrogate/factless/meaningless keys for a long long time. I like the construction consistency, but I have recently seen what might be a flaw in the surrogate key for everything idea.

If I have Companies, Invoices, and LineItems:

company
cmp_pk    cmp_name    cmp_cityfk
1         comp 1      1
2         comp 2      2

invhead
inv_pk    inv_no    inv_cmpfk
1         001       1
2         002       2

invline
lin_pk    lin_desc         lin_invfk
1         jam              1
2         peanut butter    2

I cannot do a single form parent-child-grandchild relation because invline does not have the cmp_pk in it. It would be required for me to query all line items from invline and filter them as the user navigated among invhead records for a particular company.