learning-it by example

#272 Data modelling 6.7 min

Data Modelling Mistakes: EAV, God Tables, No Keys

A retry loaded 3 payments twice into a table with no key, and 75 pounds became 150.

1. Three payments worth 75 pounds were loaded twice after a retry into a table with no key. What did the table report, and what happened to a table with the payment id as primary key?
2. In the entity attribute value table, every value is stored as text. What did the highest price query return?
3. In the god table example, Asha's email was changed only on order 4. What did the god table and the split design show afterwards?
4. In SQLite, which features of a typed product table, replacing an entity attribute value table, catch the mistakes?
5. What is a good habit for every table, to catch a missing key or a table doing two jobs?

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

  1. Run the three mistakes

    Copy the code folder first. Run python run.py eav, python run.py god and python run.py nokeys. For each output, write one sentence saying which number is wrong and which design avoids it. Nothing here needs the network.

    A hint

    The fixes are a typed table, a customer table and a primary key.

  2. Retry three times

    In nokeys.py, change for attempt in (1, 2): to for attempt in (1, 2, 3):. Predict the rows and total of the table with no key, and the number of rows changed when T1 is corrected, then run python run.py nokeys. When I ran it, the no-key table had 9 rows and a total of 225, 3 rows changed, and the keyed table still held 3 rows and 75.

    A hint

    Each retry adds the same three payments again.

  3. Make the god table agree

    In god.py, in the god function, change the first UPDATE so that it ends with WHERE customer_id = 'c1' instead of WHERE id = 4. Predict how many emails Asha has in the everything table, then run python run.py god. When I ran it, the count was 1 in both designs, and the rows that still know Ben were still 0 in the everything table.

    A hint

    Asha's email sits on three rows of the everything table.

  4. Give every product a numeric price

    In eav.py, change the price of P3 from "twelve" to "12" in EAV_ROWS. Predict the max price, then run python run.py eav. When I ran it, the max price was 9.5 and the sum was 33.5, even though no price looks like a word any more.

    A hint

    The values are still stored as text, and text is compared as text.

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

What this does not show

Tiny made-up data in SQLite; the fair use of EAV is not shown and no speed or size is measured.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos