Surrogate keys are artificially generated primary keys, typically integers, used to uniquely identify records.
In data warehouse you may have same record with multiple entries(this records columns value may change like country column is updated )
to identify this records you may need to use surrogate key
Natural Key (also called Business Key or Composite Key)
A natural key is a key that is formed from attributes that already exist in the real world and have a logical relationship with the data. These attributes are inherent to the entity and can be used to uniquely identify a record without needing any additional or artificial identifier.
Characteristics of a Natural Key:
- Derived from real-world data: Natural keys use actual data attributes that are meaningful in the real world. For example, an email address, Social Security number (SSN), or product serial number could be natural keys.
- Inherently unique: The natural key should uniquely identify a record without any ambiguity.
- Business logic ties: The key is meaningful within the context of the business process or data it represents.
Example of a Natural Key:
Consider a table storing customer information:
| customer_id | email_address | name |
|---|---|---|
| 1 | john.doe@email.com | John Doe |
| 2 | jane.smith@email.com | Jane Smith |
| 3 | bob.jones@email.com | Bob Jones |
In this case, the email_address can serve as a natural key because it uniquely identifies each customer, and it’s a meaningful attribute in the real world.
Pros of Natural Keys:
- Meaningful: They are meaningful in the real world and directly related to the entity.
- No extra field needed: No need to create additional columns for identification purposes.
Cons of Natural Keys:
- Changes over time: Real-world identifiers may change. For example, a person may change their email address or phone number.
- Complexity: Sometimes natural keys may involve composite keys (multiple columns), which can make the database structure more complex.
- Not guaranteed to be unique: Sometimes natural keys might not be as unique as expected (e.g., two customers having the same name in a small town).