Natural Keys
"When business-related columns are used as primary keys they are often called intelligent or natural keys."
Imagine a m-to-n relationship in a relational database. We may have users and roles, where a user can have multiple roles and a role can be assigned to multiple users.
We can model this relationship with a join table containing the primary keys of the users and roles tables.
| user_id | role_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
The natural primary key for this table would be the combination of user_id and role_id - a so-called composite key.
Compared to
Surrogate Keys, the key is derived from the data itself and doesn’t require additional columns and management.