Relational database design question - Surrogate-key or Natural-key?
Which one is the best practice and Why?
a) Type Table, Surrogate/Artificial Key
Foreign key is from user.type to type.id:
b) Type Table, Natural Key
Foreign key is from user.type to type.typeName:
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
intfield will be much more compact than one on avarcharfield.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.
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Convert SQL containing GROUP BY and SUM() to active record in CodeIgniter
- output sql data to html with hyperlink
- Update all column names to be lowercase
- SQL Server pivoting on data without an aggregate
- replace or remove characters and then cast to decimal
- Export specific rows from a PostgreSQL table as INSERT SQL script
- Query to get only numbers from a string
- SQL grouping result to half hour
- table is specified twice both as a target for INSERT and as separate source of data
- SQL to JSON Need Bracket for Array that can have multiple rows
- Find all occurrences of Substring and replace it with a special characters
- Oracle Merge statement 'ON' vs 'UPDATE' conditions performance on target table
- How to do aggregate raw sql with julian date in sqlite (with django)
- SQL Merge SOURCE order and OUTPUT order
- Notepad++/Eclipse sql code auto-indent option?
- Output of update statement does not show correct row number
- Map table to exactly one of three
- PostgreSQL - ERROR: column does not exist SQL state: 42703
- How to force evaluation of subquery before joining / pushing down to foreign server
- SQL SELECT TOP 1 FOR EACH GROUP
- Convert a date to timestamp in a join with interpolation
- How To CAST substring as a DATE
- batch update a column using sql
- Firebird SQL add empty rows in resultset
- How do I cast a timestamp column as a MM/DD/YYYY in BigQuery
- Parsing JSON with Oracle SQL without knowing incoming field names
- Selecting from random column in PL/SQL
- Simple Query to Grab Max Value for each ID
- How to programmatically check if row is deletable?