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.