#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.
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.
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.
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.
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
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK