One of the most confusing concepts in data modeling is understanding the difference between ProductKey and ProductAlternateKey.
Simple Explanation:
- ProductKey = The permanent internal ID (Surrogate Key)
- ProductAlternateKey = The business-facing code (Natural Key / SKU / Model Number)
ProductKey almost never changes. ProductAlternateKey can change over time.
Real-Life Example: One Bicycle Over 5 Years
| ProductKey | ProductAlternateKey | Product Name | Start Date | End Date | Current? |
|---|---|---|---|---|---|
| 1001 | BIKE-MTB-001 | Mountain Bike Pro | 2021-01-01 | 2022-06-30 | No |
| 1001 | BIKE-MTB-2022 | Mountain Bike Explorer 2022 | 2022-07-01 | 2023-12-31 | No |
| 1001 | MTB-X3 | X3 Trail Master | 2024-01-01 | 2024-08-31 | No |
| 1001 | MTB-ADV-2025 | Adventure Mountain Bike 2025 | 2024-09-01 | 9999-12-31 | Yes |
Why This Design is Important:
- Even though the product name and code changed 4 times, it is still the same bicycle.
- All sales, inventory, and repair history can be linked using the stable ProductKey (1001).
- The ProductAlternateKey changes because the company updates model names, SKUs, or branding.
This technique is called Slowly Changing Dimension (SCD) Type 2 — it allows us to keep the full history while maintaining data integrity.