PrimaryKey Composition

[ongoma]

Hi friends,

I have several tables where the uniqueness of a record
is only guaranteed by compining two or more fields eg.
the General Ledger monthly account balances will have a field ACCOUNT_CODE with ACCOUNT_PERIOD combined to form a primary key.

Are there any known trade-offs with such an approach or is it okay? Any alternatives?

Anwsers are highly appreciated

Ongoma

 

[Scott24x7]

Ongoma,
There are some trade-offs, which may or may not pose any long term issues for you. Compound keys (combining two or more elements to make a unique key) take slightly longer to update when appending large volumes of records, as they take longer to calculate. If you have a single unique value you can reference that is fine. However, often, in child tables, it is not at all uncommon to find two or more fields combined to create uniqueness of index, and in fact, this can be very handy if you use it correctly too, as you can find "Partial" values, and still match to a record.
For instance, lets say you have "AccountNumber" and "InvoiceNumber" in the same table. If you just want to find the first record for an account, you can simply "SEEK 12345" or SEEK oObject.AccountNumber (or whatever holds the value you want). You don't have to specify the full index in order to find it this way. It will take the "Partial" match, but ONLY on the leading digits, not the trailing ones...
Also you want to make 100% sure that combining these fields WILL create a unique index. Otherwise, you are playing with fire.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Ongoma,

The only problem with your approach is that it will make your code very slightly more complicated. Every time you write a query with the primary key in the WHERE clause, or one involving a join, you will have to combine two fields in the relevant filter or join condition. The same will be true when you do a SEEK.

But the extra work will be pretty negligible. On balance, I can see now real difficulty with this approach. In fact, it's one I used to use quite regularly myself. (I still create unique keys in this way, but I tend to use an artificial ID field now as my primary key.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[alvechurchdata]

Also you want to make 100% sure that combining these fields WILL create a unique index.

Really grill the users on this one. A lot of them don't seem to have a firm grasp on the concept of "unique". Just as an example - they'll swear that ACCOUNT_PERIOD is unique and then reuse the same numbers 1..12 next year. Or they'll reuse the same ACCOUNT_CODE for one mail order customer and for another shop customer and that's OK because shop customers don't get an invoice.

The only other point I'll raise is the hassle of concatenating the fields. If they are of different data types then you've got to make them compatible before concatenating. If the first is already a string then you've got to keep the right number of padding spaces between it and the second one.

Don't be tempted to index on something like:

[TT]AllTrim(ACCOUNT_CODE) + AllTrim(ACCOUNT_PERIOD)[/TT]

because that can throw up duplicates. A composite like "A123456789" might be "A123" + "456789" or "A1234" + "56789".


And if you're dealing with strings then you have also got to worry about upper and lower case.

I'm much happier creating surrogate integer keys. I know it's a waste of space and compromises the relational model but I'm past caring.

I take Scott's point about partial searches but I'd still rather have an integer key to ensure uniqueness and relational integrity and create another couple of regular indexes to deal with user searches.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

Hello Ongoma,

in general, I'd rather introduce some autoincrementing number field to any table as a primary key, you may still have a candidate index on combined fields just to check data integrity, but for joins and seeks the single field will normally be more comfortable. An exception are tables that build up an n:m relationship of two other tables. often then you might want to view it from the n or m side and join on each single foreign key. But even then you may have those two foreign key fields in the crossreferencing table and an additional own primary key.

Theory also states, that a primary key should have no meaning whatsoever, besides being a unique key for that record, that can be referenced as a foreign key in a child table. So a primary key should just be some numbering or even something random thing like a GUID.

And Candidate index type is there to have the check on uniqueness as some kind of data integrity check additional to some fully unrelated primary key.

Then, if you come into the situation, that the key does not really need to be unique, it's quite easy to change it to a regular index or even drop it, if that uniqueness check is all the index was made for.

I wouldn't even use an email or phone number or postal code as a primary key, although the nature of such things is to be unique.

The main aspect of defining a primary key is to be unique, but you shouldn't search for a data field or a combination of fields in the record that is/are unique, simply introduce an extra ID field for the primary key. You can't go wrong.

Bye, Olaf.

 

[ongoma]

Time Zone difference: I was a sleep!

Thanks guys. I think my concept of primary keys was rather misplaced. I need to do some sort of reading. I have to change my approach this issue.

What I have learnt from this thread is enough to get me started.

Thanks to all OF YOU!

Ongoma