Primary keys
Should I use surrogate keys or natural keys? What are they and which setup will give me the least amount of development issues?
What are natural keys?
A column (or a combination of columns) that has business meaning and is inherently unique.
Examples of natural keys:
| Table | Natural Key Example |
|---|---|
| Users | email |
| Products | SKU (Stock Keeping Unit) |
| Countries | ISO Country Code (US, IN, UK) |
| Employees | SSN (Social Security Number) |
What are surrogate keys?
Usually auto-generated. e.g. 1 for the first record, 2 for the second, 3 for the third and so on.
Details on usage
- Use consistent naming e.g. <table_name>_id
- Also use them on many-to-many junction tables even if it just consists out of 2 surrogate keys from 2 other tables (this table might later grow to have more fields and this will make joins easier since you don't have to worry about composite keys).