learning-it by example

#253 Data modelling 6.5 min

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.

1. When a source system renumbered three made-up customers, which SQLite key design still showed all four orders under the customers' new codes?
2. After a source renumbered 3 customers and the new extract was loaded, how many customer rows did a natural key table and a hash key table each hold in SQLite?
3. In data modelling, why is a hash key called a natural key in disguise?
4. In a SQLite customer table with a database-assigned surrogate id and a hash key computed from the customer code, what happened to Asha's surrogate id and hash key when the same three customers were loaded in reverse order?
5. Why does the hash key function trim the code and make it upper case before hashing?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the keys and the change

    Copy the code folder first. Read keys.py and change.py, then write down what you expect python run.py keys and python run.py change to print. Run both and compare. In the change run you should see the natural rename rejected, 6 customers for 3 people, and 4 of 4 surrogate orders joined.

    A hint

    Think about which designs match the new codes to the existing rows.

  2. Shorten the hash

    In keys.py, change hexdigest()[:16] to hexdigest()[:8] in hash_key. Predict what Asha's hash column looks like, then run python run.py keys. You should see 0897b64a, the first 8 characters of the earlier value. A real table should store the whole value.

    A hint

    The slice only cuts the text, it does not change the hash.

  3. Stop upper casing the code

    In keys.py, change code.strip().upper() to code.strip() in hash_key. Predict the line that compares ' c-101 ' with 'C-101', then run python run.py keys. You should see False on that line.

    A hint

    Strip removes the spaces, but 'c-101' and 'C-101' are different text.

  4. Add a fourth customer

    In keys.py, add a fourth customer to CUSTOMERS: ("C-104", "dee@example.com", "Dee"). Predict Asha's surrogate id when the list is loaded in reverse, then run python run.py keys. You should see Asha surrogate 4 and the same hash as before.

    A hint

    Reverse order means Dee is loaded first.

These tasks are for you to try on your own machine. Nothing is checked or saved.

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.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos