learning-it by example

Back to the video

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

  1. 0:00 A source renumbered 3 customers. Two key designs ended up with 6.
  2. 0:30 The idea
  3. 0:57 Setup
  4. 1:15 The data and the hash key
  5. 1:45 Three designs
  6. 2:19 Loading the orders
  7. 2:43 The source renumbers
  8. 3:11 The surrogate update
  9. 3:28 Run the keys
  10. 4:28 What this does not show
  11. 4:52 Mistakes to avoid
  12. 5:36 My take and next steps
  13. 6:08 Recap

Files

  • change.py (30 lines)
  • keys.py (54 lines)
  • run.py (39 lines)

Download the code (zip)

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

Learning IT by Example.