Normalising into Lookup Tables
Try it: Normalising into Lookup Tables
How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.
How it works
- For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
- SELECT the text's id back — new or existing.
- Store that integer id (a foreign key) in the next table instead of the text.
- Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
- A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.
Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.
Loading the simulation…