Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can easily be found and decisions made by whoever would like to search for them.

This question is more about performance of the keys (or lack thereof)

I work as a DB manager, and when I create my tables I attempt to use a 'natural' key for the table. Often times this comes out as being a set of 2,3, or sometimes 4 fields that act as the primary key for the given table. More often than not these fields are Varchar in nature, but short (10 or 15 characters in length at most). Personally I try to keep them shorter!

My question is this.

Imagine I have a table that holds demographic data. The only way I can ensure that I have uniqueness in each row is to use the fields for FirstName FamilyName DateOfBirth PlaceOfBirth

(You may wonder why I include 'place of birth', I am aware of another individual (who used to live nearby - same phone number, different dialing code) with whom I shared all these details (I assume that the PlaceOfBirth was different, but I guess I could have used MothersMaidenName ;) )

so now I have an interesting problem.

I could use a much shorter field that is created from concatenating the info in the 4 main fields example: DateOfBirth first 2 characters of FirstName first 2 characters of FamilyName first 2 character of PlaceOfBirth

My question is this.

At what point would the concatenation of field provide a performance improvement over using the fields directly, ie how many columns.

I know from searching that most DBMS's have a 'thoretical maximum size limit' dependent on the B-Tree that is created. I am assuming that I don't hit this limit in terms of the length/size of the primary key.

My reason for considering using this type of 'contrived' key is: The information in the concatenated column is most likely sufficient to be able to identify the record without the need to extract all the primary key fields (would this be better for performance or no different compared to using all the 4 primary key fields?)

This is obviously a fairly 'theoretical' question, but I have considered doing this concatenation on a table that end up with 4 varchar fields, and it was obvious that uniqueness would be described by using just a shortened version. Obviously there is an effort to create this field in the first place, but in others opions would this effort be worthwhile, and at what point would it become more interesting.

I have searched for this but I have never found this question asked directly, it allays comes out as a 'natural' or 'artificial' primary key discussion.

Of course if this feels like a 'natural' or 'artificial' key discussion feel free to say so. My feeling is that this 'contrived' key would proffer the advantages of a both. Has anyone used this idea in a real world solution?

Thanks in advance for your thoughts.

David

Edit. I just found this thread

http://stackoverflow.com/questions/3735390/best-primary-key-for-storing-urls

It seems to cover similar ground, I must admit I hadn't thought of 'hashing' my columns together (mainly because they are short by nature), but I do like the idea. I guess you could do that and hash the whole row !

Edit2.

I've returned to this question just to see if there has been any change to the answers, or extra comments. I've decided to accept a response, but would like to note that I have found all the responses helpfull in the terms of the discussion.

share|improve this question

migrated from stackoverflow.com Mar 9 '12 at 12:30

This question came from our site for professional and enthusiast programmers.

    
Have you read any discussions about "intelligent keys"? –  onedaywhen Mar 9 '12 at 10:55
1  
You cannot assume the uniqueness of "FirstName FamilyName DateOfBirth PlaceOfBirth". You can have duplicates. –  A-K Mar 9 '12 at 16:20
    
@onedaywhen Intelligent keys are a new term for me, I'll go have a look, thanks. –  DaveM Mar 14 '12 at 13:21

3 Answers 3

up vote 4 down vote accepted

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

  • Some natural keys are useful in their own right: ISO currency and country codes
  • A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
  • If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Related on dba.se: http://dba.stackexchange.com/q/13778/630

share|improve this answer

In my own experience every time I've run into one of these contrived keys, while it might look like a good idea on paper, they've always caused problems. Essentially it's a form of denormalisation if Family Changes ie some one get's married or divorced, now you either change it in both, or you you lose how it was contrived. Unless I'm held at gun point I always choose data integrity over performance.

share|improve this answer
    
WMD's, data integrity or performance, I'd never looked at it like that... I wonder how long it will take the NSA to find this remark –  DaveM Mar 14 '12 at 13:27

It would be truly awful database architecture to use Compound Keys or to use a concatenated compound key as you propose. For compound keys, any tables with a foreign key reference to your demographic data would also require columns to point to FirstName FamilyName DateOfBirth PlaceOfBirth.

Concatenating the data into one column is a terrible idea - you're going to be using VARCHAR(~256) for your Primary Key and Foreign Key references. This will make your indexes huge and performance will suffer. You'll also need to parse and join to get the actual data - this is error prone as Kevin Andersen New York is not the same as New York Kevin Andersen.

You should be using a surrogate key - a key that has no context in your business model (long/bigint or a GUID).

Look at Facebook's data model:

https://graph.facebook.com/cocacola

notice the ID is a surrogate key represented by a number which has no context in the data - 40796308305

share|improve this answer
    
You've conveniently side-stepped the question, presumably in order to preach, "Always use surrogate keys." As a reminder, the question is, "At what point would the concatenation of field[sic] provide a performance improvement over using the fields directly?" –  onedaywhen Mar 9 '12 at 10:17
    
And I explained the trade-off - both are bad for performance compared to surrogate keys. –  reach4thelasers Mar 9 '12 at 10:19
    
Your first sentence: did you really mean to say "architecture"? All SQL products allow compound keys! Your second sentence: that's only how SQL is supposed to work logically. Sybase SQL Anywhere, for example, would use a pointer rather repeating all four columns; Teradata uses hashing for long keys. –  onedaywhen Mar 9 '12 at 10:54
    
The databse server wasn't specified. I was answering thinking about MS SQL Server. –  reach4thelasers Mar 9 '12 at 11:03
    
I was hoping for responses that would give usefull links and informatino on how different DB engines handle multi column indexes, so thanks to both onedaywhen and reach4thelasers –  DaveM Mar 14 '12 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.