I’m not a data architect or DBA, but in my current and past positions I, like most software developers, have been responsible for designing schemas for both simple and complex databases.  One thing I always waffle about is whether to compose keys from meaningful data or generate surrogate keys for my tables.

I have always been a proponent of using surrogate, or blind, keys.  The reason is that I have faced scenarios where what is said to be an iron-clad rule about the business meaning associated with a primary key suddenly changes requiring the need to change that primary key – most DBs don’t handle this well.  It’s also cumbersome constructing joins on natural keys when they are composed of several columns.   With that said, I do see advantages to having real meaning attached to the fields used as a composite key.

What do you think?  Is this a matter of preference of the database designer or is there some rule or advantage that I don’t know of that would be a definite answer one way or another?

Here are a couple of articles to refer to for arguments.

http://www.bcarter.com/intsurr1.htm

http://searchsqlserver.techtarget.com/general/