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…