List:General Discussion« Previous MessageNext Message »
From:Daniel Walker Date:May 16 2005 11:02am
Subject:Re: Single vs Multiple primary keys
View as plain text  
On Sunday 15 May 2005 20:31, Dan Bolser wrote:
> You must mean a multipart primary key with three parts :)
> or "multiple-column indexes"
> That is what I would do (use a multiple-column index (primary key)  - its
> kinda based on opinion, but I think you should let the real data be the
> primary key where appropriate, and avoid artificial 'auto_increment'
> unless they are specifically useful or necessary in your situation.
> I.e build the database around the data, not the other way round :)
> That is just my design preference though.
> Not sure about performance problems, but you get two 'indexes' for free
> with one multipart primary key with three parts (so the order of the
> parts is significant (depending on your application)).

I would advocate quite the opposite. The data is the data: primary/foreign 
keys are data about the database. You should always separate the two. For 
instance, the foreign key values used in a junction table, used to manage 
many-to-many relationships, are _simply_ foreign keys; the need for their 
presence in that particular table has more to do with normalisation and good 
database design than anything about the actual data in the real world. By all 
means, build the database AROUND the data, but don't actually USE the data to 
build the database.

You can never really guarantee the uniqueness (or availability) of the data 
that you select for your primary key when you use _real_ data. A classic 
example, is where someone is using National Insurance numbers for employees 
an Employee database as the primary key for each employee - what happens if 
you suddenly start hiring foreign contractors, where no such data exists? Do 
you start inventing false data, just to satisfy your need for a foreign key? 
If you'd used auto-increment fields, the problem wouldn't arise.

Furthermore, auto_increments are just integers: there is very little overhead 
involved in handling them. Real data is usually either more complex, or is 
apt to become so at some point in the future.

Daniel Walker
Single vs Multiple primary keysBob)15 May
  • Re: Single vs Multiple primary keysMartijn Tonies15 May
    • Re: Single vs Multiple primary keysDan Bolser15 May
      • Re: Single vs Multiple primary keysDaniel Walker16 May