Saturday, June 13, 2015

Synthetic key vs Natural key in DB world

One of the decade old debates in SQL world:
                        Which is preferred primary key- synthetic or natural?

Both have their own pros and cons, so choosing one over other is not trivial. But you can definitely do one thing to make your life easier, whichever approach you (and your team) decides; let's stick to that and try to avoid mixing these two strategies.

Natural Key

The word relational used in DB/SQL world basically means that while designing tables your need to consider how the columns relate to each other. This way all columns together will define one entity. So conceptually it makes sense to have one or more than one column from the entity to make rows unique. But sometime over a period it becomes difficult to maintain this condition and we are forced to changed the schema of table to ensure that primary or composite key(primary key composed of multiple columns)  really remain unique.

Synthetic/Surrogate/Made-Up/Artificial Key

Synthetic is a generated unique value that is used as primary key of a database table. So it's one of the columns of the table but it doesn't have any logical relationship with the rest. But good thing is that a single synthetic key can ensure that all rows have a unique key.  It's a sequence generated by an algorithm which ensures its uniqeness. This is definitely better than managing multiple columns for a key. 


References
http://www.dbta.com/Columns/DBA-Corner/Surrogate-Keys-or-Natural-Keys-84892.aspx
http://stackoverflow.com/questions/4960263/sql-primary-key-column-artificial-id-column-vs-natural-columns

No comments:

Post a Comment