September 10, 2005 at 4:35 am
Recently I have had discussion with my colleagues regarding the best database design practicies. Our opinions regarding whether to use composite keys as a primary key (as opposed to one-field-key) splitted into two diametrically opposite groups. I am just wondering - is there any "scientific"/ technology recommendations / comparisons between these two solutions.
Any input would be highly appreciated.
September 13, 2005 at 8:00 am
This was removed by the editor as SPAM
November 30, 2009 at 3:21 am
Hi,
Even i am having a doubt on the same issue. I am having a table in which i want the combination of some fields to be unique. I was using a single primary key which was an auto int. I tried to replace the primary key with a composite key which resulted in a performance drop in my application.
I am sorry that i couldn't give you a solution but another question. Will there be a difference in the performance in terms of retrieving data between Primary Key and Composite key??
December 1, 2009 at 3:33 pm
shekihan (9/10/2005)
RecentlyI have had discussionwith my colleaguesregarding the best database design practicies. Our opinions regarding whether to use composite keys as a primary key (as opposed to one-field-key)splitted into two diametrically opposite groups. I am just wondering - isthere any"scientific"/ technology recommendations/ comparisons between these two solutions.
Any input would be highly appreciated.
I think the question as to choice of primary key often comes down to how you plan to use the data. If most of your queries are going to be based on table data which comprises a candidate key, it is probably a good choice for primary key even if it is composite. Beyond that, there are lots of pros and cons worth considering but ultimately there is probably no single choice which is always preferrred.
Intuitively, one would imagine that a composite key will often provide lower performance than a single key of the same size depending upon type. For some types, like character strings, a composite key probably behaves similarly to a single key of the same size since sort orders naturally fall the same way
(e.g. searching for 'AB'+'CD' is likely the same as searching for 'ABCD'). But consider a composite key made up of a string and an integer -- likely the key components are evaluated separately. Floating point data is probably even harder to search on (aside from indeterminate values) because the internal representation is composite (sign/mantissa/exponent) and when used as part of a composite key, who knows what hoops the code which traverses the index has to jump through for optimal searching.
Often the question comes down to the pros and cons of using a natural key vs. using a surrogate key. here's a pretty good article discussing that topic:
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
One thing not mentioned in that article, however, is the problem of surrogate keys and uniqueness. Basically, if you use a natural key, the primary key constraint will guarantee that your records are unique. If you use a surrogate key, however, since it has no relationship to the data, you run the risk of having a record entered twice but with different surrogate keys. To avoid this, you generally should have a UNIQUE constraint on the candidate key which would have formed the natural primary key. This defeats some of the size advantages of using a surrogate key but the performance improvements alone are probably worth it.
There's a lot to consider here -- I doubt that there is a one-size-fits-all solution.
- Les
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply