Database Normalization

Normalization is a set of general relational-database design guidelines — not specific to SQLite — for organizing tables so that each piece of data lives in exactly one place. The guidelines are expressed as a series of normal forms, each building on the one before it. We'll walk through the first three, the ones you'll use on almost every schema, all grounded in RecipeBox.

First Normal Form (1NF): Atomic Values

1NF requires every column to hold a single, atomic value — no repeating groups and no comma-separated lists stuffed into one field. Imagine RecipeBox stored ingredients like this:

-- Violates 1NF: ingredients is not atomic, it's a hidden list
CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT,
  ingredients TEXT -- 'flour, sugar, eggs, butter'
);

This looks convenient at first, but it breaks down fast: you can't find every recipe that uses 'eggs' without a fragile LIKE search, you can't count ingredients cleanly, and you can't attach a quantity or unit to each one. The fix is to give ingredients their own table, one row per recipe-ingredient pair:

CREATE TABLE ingredients (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER NOT NULL REFERENCES recipes(id),
  name TEXT NOT NULL,
  quantity TEXT
);

Now each row holds one atomic fact, and 'eggs' is a plain, indexable, queryable value.

Second Normal Form (2NF): No Partial Dependency

2NF applies specifically to tables with a composite primary key (a key made of more than one column) — every non-key column must depend on the whole key, not just part of it. Suppose RecipeBox tracked recipe-ingredient quantities in a table keyed on (recipe_id, ingredient_id), but also stored the ingredient's name there:

-- Violates 2NF: ingredient_name only depends on ingredient_id, not the full (recipe_id, ingredient_id) key
CREATE TABLE recipe_ingredients (
  recipe_id INTEGER,
  ingredient_id INTEGER,
  ingredient_name TEXT,
  quantity TEXT,
  PRIMARY KEY (recipe_id, ingredient_id)
);

ingredient_name really only depends on ingredient_id — it's the same no matter which recipe you're looking at — so it's a partial dependency on the composite key. The fix: move ingredient names into their own ingredients master table, and keep only quantity (which genuinely depends on the whole recipe+ingredient pair) in the join table.

Third Normal Form (3NF): No Transitive Dependency

3NF says every non-key column must depend on the primary key directly — not on another non-key column. Suppose recipes stored the author's name alongside their user id:

-- Violates 3NF: author_name depends on user_id, which depends on recipes.id — a transitive dependency
CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT,
  user_id INTEGER,
  author_name TEXT -- duplicated from users.name on every recipe row
);

author_name depends on user_id, and user_id depends on recipes.id — so author_name depends on the key only transitively, through user_id. If a user renames themselves, every one of their recipe rows now needs updating or the data goes stale and inconsistent. The fix is to drop author_name entirely and join to users whenever you need it:

SELECT r.title, u.name AS author_name
FROM recipes r
JOIN users u ON u.id = r.user_id;

The Ingredients Table vs. a JSON Column

The previous lesson showed RecipeBox storing a recipe's tags as a JSON array in one column. Is that a 1NF violation too? In spirit, yes — a JSON array is still a repeating group, just encoded differently. The difference is that SQLite's JSON functions (json_each(), json_extract()) let you query into it reasonably well, which a plain comma-separated string doesn't. So the real question becomes a tradeoff, not a strict rule:

Denormalization Is Sometimes the Right Call

Normalization isn't a moral rule — it's a tool for avoiding update anomalies and duplicated data. In an embedded, single-device context like a mobile app backed by SQLite, a little deliberate denormalization can be a completely reasonable tradeoff: storing recipe_ratings.avg_rating as a real cached column (updated by a trigger, covered later in this track) instead of recomputing it via a view on every screen load can matter for a phone's battery and CPU in a way it wouldn't on a beefy server. The key is that it's a conscious choice made after understanding the normalized design — not a shortcut taken out of not knowing better.