Question

Some database designers think that, for consistency, if one table has a surrogate key, all tables in the database should. Others think that such a policy is too rigid. Which position do you support and why? Try to illustrate with an example.

Looking for opinions supporting either position! Just want to wrap my head around each concept's pros and cons.

Thank you!

Answer 1

Surrogate key is a key that can be used to represent an entity or object that are in the database  or outside of the database.It is also available or visible for the application and users who use this.It is mainly used with modeled object.It is also a unique key.This key is generated by the system itself.This key value cannot be altered by the user .The used surrogate keys are incremented automatically.It doesn't have a contextual meaning.If a surrogate key is used , it should be null.Always try to reduce the excessive use of surrogate keys.

• Advantages of using Surrogate key :
• It is used as a substitute to natural keys.
• It can help us to implement slowly changing dimensions which are called as SDC.
• Since these auto generated small values of type integer , it is easier to use.Because it acts a small index.Therefore it can give better performance.
• Using Surrogate key makes our work easier and faster for joining two or tables.
• ETL transformation can be made easier by using these keys . ETL means Extract , Transform and Load .
• It can be used to integrate data's of different categories .
• If data is in different category and if this data's doesn't have natural keys , we can use surrogate keys instead of it.
• Disadvantages of surrogate keys :
• ​​​​​​​Try to minimize the over usage these keys , because it doesn't have a special meaning there ,sometimes it will be a burden.
• Migrating data from one table to other or from one database to other will be difficult .Because association of database sequence with this key's attribute.
• It will increase the complexity for using  ETL(Extract ,Transform ,Load ) framework.
• If these keys are not managed correctly , it will effect the performance of inserting values to table.
• The space will be wasted due to unwanted use of surrogate keys.
• Transferring of data from one system to the other will be difficult.