Database Design – Designing Primary Key

One of the key of designing a database is picking the right primary key. I keep in mind several things when I design a database:

• If possible, primary key should not have a business meaning.
  Business meaning changes, some attribute that is unique now, may not be unique in the future.
• Related to that point above, create a surrogate key.
  Most of the time (95%), a surrogate key helps when improving an existing database, there would be less update, since the surrogate key does not have business meaning.
• Get a simple data type as primary key
• Generally, avoid composite primary key, especially with table that has high volume of transactions.
  Having a composite primary key with clustered index may cause pagination, it is also make it more challenging in the future if a child table is added to that table.

There are, of course, exceptions to those thing above, those are just guidelines…