Relational database design question – Surrogate-key or Natural-key?

by

I believe that in practice, using a natural key is rarely the best option. I would probably go for the surrogate key approach as in your first example.

The following are the main disadvantages of the natural key approach:

  • You might have an incorrect type name, or you may simply want to rename the type. To edit it, you would have to update all the tables that would be using it as a foreign key.

  • An index on an int field will be much more compact than one on a varchar field.

  • In some cases, it might be difficult to have a unique natural key, and this is necessary since it will be used as a primary key. This might not apply in your case.

More Related Contents:

  1. Recommended SQL database design for tags or tagging [closed]
  2. Are soft deletes a good idea? [duplicate]
  3. What mysql database tables and relationships would support a Q&A survey with conditional questions? [closed]
  4. single fixed table with multiple columns vs flexible abstract tables
  5. DB design to use sub-type or not?
  6. What are the principles behind, and benefits of, the “party model”?
  7. Best way to model Customer Address
  8. how to create this table in sql server?
  9. How to create multiple one to one’s
  10. SQL – How to store and navigate hierarchies?
  11. Safely rename tables using serial primary key columns
  12. A beginner’s guide to SQL database design [closed]
  13. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  14. How to design a database for User Defined Fields?
  15. Dynamic Database Schema [closed]
  16. Storing sex (gender) in database
  17. Should I design a table with a primary key of varchar or int?
  18. Composite Primary key vs additional “ID” column?
  19. Max length for client ip address [duplicate]
  20. When to Denormalize a Database Design
  21. Should I use SQL_Variant data type?
  22. Postgres constraint for unique datetime range
  23. MySQL – how to front pad zip code with “0”?
  24. SQL: Using NULL values vs. default values
  25. What does ON [PRIMARY] mean?
  26. What is the best way to implement soft deletion?
  27. Is there a severe performance hit for using Foreign Keys in SQL Server?
  28. How important are lookup tables?
  29. Historical / auditable database
  30. Designing SQL database to represent OO class hierarchy

Leave a Comment