Primary Keys: Natural, Surrogate and Hash Keys
A source renumbered 3 customers; natural and hash keys ended up with 6 rows, a surrogate key kept 3.
Video #253 ยท Data modelling
Steps in the video
- 0:00 A source renumbered 3 customers. Two key designs ended up with 6.
- 0:30 The idea
- 0:57 Setup
- 1:15 The data and the hash key
- 1:45 Three designs
- 2:19 Loading the orders
- 2:43 The source renumbers
- 3:11 The surrogate update
- 3:28 Run the keys
- 4:28 What this does not show
- 4:52 Mistakes to avoid
- 5:36 My take and next steps
- 6:08 Recap
Files
- change.py (30 lines)
- keys.py (54 lines)
- run.py (39 lines)
Tested with
Python 3.12.15
What this does not show
Three made-up customers in SQLite; only the surrogate load matches people by email (assumed unchanged), the other two are plain inserts; no speed or size is measured.
Sources
- https://docs.python.org/3/library/hashlib.html
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-hub
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-integration-keys
- https://en.wikipedia.org/wiki/Natural_key
- https://en.wikipedia.org/wiki/Surrogate_key
- https://www.sqlite.org/autoinc.html
- https://www.sqlite.org/foreignkeys.html
- https://www.sqlite.org/lang_createtable.html
Learning IT by Example.