Trainee

Jan 02, 2009
Rating

Not so fast
by: Aaron Bingham

I completely disagree. You don't need to add a numeric ID unless you have an external requirement for one (except for rare exceptions, see below).

You must define at least one key for every table (unique constraint or primary key). This key can span multiple columns. Often, the key _must_ span multiple columns because of the nature of the data. If you cannot identify such a key, you don't understand your data yet. Study it again.

Overuse of surrogate keys can cause you trouble when:
* you forget to define any other keys (unique constraints) on the real data and you end up with duplicate data
* you are forced to perform many unnecessary joins, complicating queries and degrading performance
* you lack data from the key in a referring table that would allow definition of better constraints
* etc.

A numeric surrogate key (a key which does not arise from the external requirements) can be considered in certain circumstances exceptional circumstances. Cases where you might consider a surrogate key are:
* when a concrete performance bottleneck has been identified which cannot be addressed by other means (e.g., defining an index)
* when writing queries proves too cumbersome
* when repeating multiple columns of the key in referring tables threatens to cause a maintenance headache.
Don't jump into this too fast.

X Close Editor

Jan 02, 2009
Rating

Submitted by: Aaron Bingham

Title

Comment
I completely disagree. You don't need to add a numeric ID unless you have an external requirement for one (except for rare exceptions, see below). You must define at least one key for every table (unique constraint or primary key). This key can span multiple columns. Often, the key _must_ span multiple columns because of the nature of the data. If you cannot identify such a key, you don't understand your data yet. Study it again. Overuse of surrogate keys can cause you trouble when: * you forget to define any other keys (unique constraints) on the real data and you end up with duplicate data * you are forced to perform many unnecessary joins, complicating queries and degrading performance * you lack data from the key in a referring table that would allow definition of better constraints * etc. A numeric surrogate key (a key which does not arise from the external requirements) can be considered in certain circumstances exceptional circumstances. Cases where you might consider a surrogate key are: * when a concrete performance bottleneck has been identified which cannot be addressed by other means (e.g., defining an index) * when writing queries proves too cumbersome * when repeating multiple columns of the key in referring tables threatens to cause a maintenance headache. Don't jump into this too fast.

Cancel Delete Delete & Ban

throbber Updating Comments page...