> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.

# Designing a Relational Database

> From one messy sheet to clean tables: a visual guide to primary keys, relationships and normalization (1NF, 2NF, 3NF).

export const QUIZ = [{
  q: "You change Ana's phone in one row of the sheet, but two other rows still have the old number. What is this called?",
  a: ["An update anomaly", "An insert anomaly", "A foreign key error"],
  c: 0,
  why: "The same fact was stored in three places and only one was changed. Storing each fact once makes this impossible."
}, {
  q: "Why is customerID a better primary key than phone?",
  a: ["It is shorter", "It never needs to change and is never empty", "Phone numbers can't be indexed"],
  c: 1,
  why: "Ana can change her phone or leave it blank. A number with no real-world meaning has no reason to change, so nothing that points at it breaks."
}, {
  q: "An order holds many products and a product appears in many orders. How do you store that?",
  a: ["Add a product column to Orders", "Add a junction table with orderID and product", "Put a list of products in one cell"],
  c: 1,
  why: "OrderDetails has one row per order–product pair. It turns one many-to-many into two one-to-many links."
}, {
  q: "The key is (orderID, product), and price depends only on product. Which rule does this break?",
  a: ["1NF", "2NF", "3NF"],
  c: 1,
  why: "2NF says every non-key column must depend on the whole key. price depends on only part of it, so it repeats for every order of that product."
}, {
  q: "Orders stores customerID and phone. phone depends on customerID, not on the order. Which rule does this break?",
  a: ["1NF", "2NF", "3NF"],
  c: 2,
  why: "3NF says non-key columns depend on the key and nothing else. phone depends on another non-key column, so it moves to Customers."
}];

export const DESIGN_STEPS = [{
  title: "Decide the purpose",
  do: "List the questions people will ask and the reports they will read.",
  rule: "Anything a report needs must be stored somewhere.",
  shop: "Who ordered what, when, and for how much."
}, {
  title: "Split into tables, pick keys",
  do: "One table per subject. Give each a primary key.",
  rule: "A key is unique, never empty and never changes. Use a meaningless ID.",
  shop: "Customers, Orders, Products, keyed by customerID, orderID, product."
}, {
  title: "Connect the tables",
  do: "For each pair of subjects, decide: one-to-many, many-to-many or one-to-one.",
  rule: "Foreign key on the “many” side. Many-to-many gets a junction table. One-to-one shares the key.",
  shop: "Orders → Customers. OrderDetails links Orders and Products."
}, {
  title: "Normalize",
  do: "Check every table against the normal forms, and split where a fact repeats.",
  rule: "1NF one value per cell. 2NF the whole key. 3NF nothing but the key. BCNF: what decides a column is a key.",
  shop: "One messy sheet became four tables; each fact is stored once."
}];

export const IDX_SORTED = [["Ana", 2], ["Ana", 8], ["Ben", 6], ["Dan", 9], ["Kim", 5], ["Leo", 4], ["Mia", 1], ["Raj", 3], ["Zoe", 7]];

export const IDX_ROWS = [[1, "Mia"], [2, "Ana"], [3, "Raj"], [4, "Leo"], [5, "Kim"], [6, "Ben"], [7, "Zoe"], [8, "Ana"], [9, "Dan"]];

export const GATE = [{
  label: "Insert 0 pens into order 1003",
  row: [1003, "Pen", 0],
  line: "qty",
  ok: false,
  why: "CHECK (qty > 0) says no. Zero pens is not an order line."
}, {
  label: "Insert a stapler into order 1003",
  row: [1003, "Stapler", 1],
  line: "product",
  ok: false,
  why: "Products has no Stapler row, so the foreign key would point at nothing."
}, {
  label: "Insert a mug with no order",
  row: [null, "Mug", 1],
  line: "order",
  ok: false,
  why: "orderID is NOT NULL: it is part of the primary key, so it can't be empty."
}, {
  label: "Insert pens into order 1001 again",
  row: [1001, "Pen", 3],
  line: "pk",
  ok: false,
  why: "Order 1001 already has a Pen line. The primary key allows one row per order and product."
}, {
  label: "Delete order 1001 (RESTRICT)",
  del: 1001,
  mode: "RESTRICT",
  line: "order",
  ok: false,
  why: "Two lines still point at order 1001, so the delete is refused."
}, {
  label: "Delete order 1001 (CASCADE)",
  del: 1001,
  mode: "CASCADE",
  line: "order",
  ok: true,
  why: "The order and its two lines are deleted together. Nothing is left pointing at nothing."
}, {
  label: "Insert 1 mug into order 1003",
  row: [1003, "Mug", 1],
  line: "",
  ok: true,
  why: "Every rule passes, so the row is stored."
}];

export const GATE_BASE = [[1001, "Pen", 2], [1001, "Notebook", 1], [1002, "Mug", 1], [1003, "Notebook", 3]];

export const DDL = [{
  id: "",
  text: () => "CREATE TABLE OrderDetails ("
}, {
  id: "order",
  text: mode => `  orderID INT NOT NULL REFERENCES Orders ON DELETE ${mode},`
}, {
  id: "product",
  text: () => "  product TEXT NOT NULL REFERENCES Products,"
}, {
  id: "qty",
  text: () => "  qty INT CHECK (qty > 0),"
}, {
  id: "pk",
  text: () => "  PRIMARY KEY (orderID, product)"
}, {
  id: "",
  text: () => ");"
}];

export const DENORM_OPS = [{
  label: "Read order 1001's total",
  sql: ["SELECT SUM(d.qty * p.price)\nFROM OrderDetails d JOIN Products p USING (product)\nWHERE d.orderID = 1001;", "SELECT totalPrice FROM Orders\nWHERE orderID = 1001;"],
  reads: [4, 1],
  writes: [0, 0],
  note: ["Reads 2 order lines and 2 prices, then adds them up.", "Reads one stored number. Faster and simpler."]
}, {
  label: "Change pens to 5",
  sql: ["UPDATE OrderDetails SET qty = 5\nWHERE orderID = 1001 AND product = 'Pen';", "UPDATE OrderDetails SET qty = 5\nWHERE orderID = 1001 AND product = 'Pen';\nUPDATE Orders SET totalPrice = 11.50\nWHERE orderID = 1001;"],
  reads: [0, 0],
  writes: [1, 2],
  note: ["One write. The total is worked out again on the next read.", "Two writes: the line and its copy. Both must succeed together."]
}, {
  label: "Change pens to 5, skip the total",
  sql: ["UPDATE OrderDetails SET qty = 5\nWHERE orderID = 1001 AND product = 'Pen';", "UPDATE OrderDetails SET qty = 5\nWHERE orderID = 1001 AND product = 'Pen';\n-- forgot: UPDATE Orders SET totalPrice …"],
  reads: [0, 0],
  writes: [1, 1],
  note: ["Nothing to forget: the total is never stored.", "The copy is now wrong. The order says 7.00 but its lines add up to 11.50."]
}];

export const BCNF_STEPS = [{
  tab: "The table",
  nf3: "",
  bcnf: "",
  cell: (r, j) => j < 2 ? "is-on" : "",
  lines: [["Rule 1", "A student takes each course with one teacher. So student + course picks exactly one row. That pair is the key (orange)."], ["Rule 2", "Each teacher teaches only one course: Tan always means MATH101. But a course can have several teachers: MATH101 has Tan and Kim."]]
}, {
  tab: "3NF check",
  nf3: "pass",
  bcnf: "",
  cell: (r, j) => j === 2 ? "is-on" : "",
  lines: [["3NF asks", "Is every column outside the key about the key?"], ["Here", "Only teacher is outside the key (orange). Ana + MATH101 tells you the teacher: Tan. So the table passes 3NF."], ["Blind spot", "3NF never looks at course, because course is part of the key."]]
}, {
  tab: "BCNF check",
  nf3: "pass",
  bcnf: "fail",
  cell: (r, j) => r[2] === "Tan" ? j === 2 ? "is-on" : j === 1 ? "is-hot" : "" : "",
  lines: [["BCNF asks", "If one column tells you another, is it a key?"], ["Here", "teacher tells you course: see Tan, and you know MATH101. But teacher is not a key: Tan is on three rows. So the table fails BCNF."], ["Cost", "“Tan teaches MATH101” is stored three times (red). Move Tan to another course and all three rows must change."]]
}, {
  tab: "Split",
  nf3: "pass",
  bcnf: "pass",
  lines: [["Fix", "Give Rule 2 its own table, keyed by teacher. Enrolment keeps who studies with which teacher."], ["Result", "“Tan teaches MATH101” is stored once. Moving Tan changes one row."]]
}];

export const ENROLMENT = {
  name: "Enrolment",
  cols: ["student", "teacher"],
  key: [0, 1],
  fk: [1],
  rows: [["Ana", "Tan"], ["Ben", "Tan"], ["Dan", "Tan"], ["Chi", "Kim"], ["Ana", "Lee"]]
};

export const TEACHER_COURSE = {
  name: "TeacherCourse",
  cols: ["teacher", "course"],
  key: [0],
  rows: [["Tan", "MATH101"], ["Kim", "MATH101"], ["Lee", "PHYS101"]]
};

export const TEACHING = {
  name: "Teaching",
  cols: ["student", "course", "teacher"],
  key: [0, 1],
  rows: [["Ana", "MATH101", "Tan"], ["Ben", "MATH101", "Tan"], ["Dan", "MATH101", "Tan"], ["Chi", "MATH101", "Kim"], ["Ana", "PHYS101", "Lee"]]
};

export const STAGES = [{
  tab: "Raw",
  next: "Apply 1NF",
  tables: [{
    name: "Orders",
    cols: ["orderID", "date", "customer", "phone", "items"],
    key: [0],
    hot: [4],
    rows: [[1001, "Mar 2", "Ana", "555-0101", "Pen ×2 @1.50, Notebook ×1 @4.00"], [1002, "Mar 3", "Ben", "555-0102", "Mug ×1 @8.00"], [1003, "Mar 5", "Ana", "555-0101", "Notebook ×3 @4.00"]]
  }],
  counts: [2, 2],
  rule: "1NF: one value per cell.",
  problem: "The items cell holds more than one value. Order 1001's cell has two: a pen and a notebook. So to count how many notebooks were sold, you would have to split the text by hand.",
  fix: "Give each product in an order its own row. Now you need two things to find a row: the order number and the product."
}, {
  tab: "1NF",
  next: "Apply 2NF",
  tables: [{
    name: "OrderLines",
    cols: ["orderID", "date", "customer", "phone", "product", "price", "qty"],
    key: [0, 4],
    hot: [1, 2, 3, 5],
    rows: [[1001, "Mar 2", "Ana", "555-0101", "Pen", "1.50", 2], [1001, "Mar 2", "Ana", "555-0101", "Notebook", "4.00", 1], [1002, "Mar 3", "Ben", "555-0102", "Mug", "8.00", 1], [1003, "Mar 5", "Ana", "555-0101", "Notebook", "4.00", 3]]
  }],
  counts: [3, 2],
  rule: "2NF: each column needs the whole key, not just half of it.",
  problem: "The key has two halves: orderID and product. date, customer and phone need only the orderID half: order 1001 is on Mar 2 for Ana, whatever the product. price needs only the product half: a notebook costs 4.00 in any order. Because they use only half the key, they repeat on every row that shares that half.",
  fix: "Move date, customer and phone to an Orders table, and price to a Products table. OrderLines keeps only qty: how many of this product in this order. That one fact needs both halves."
}, {
  tab: "2NF",
  next: "Apply 3NF",
  tables: [{
    name: "Orders",
    cols: ["orderID", "date", "customer", "phone"],
    key: [0],
    hot: [3],
    rows: [[1001, "Mar 2", "Ana", "555-0101"], [1002, "Mar 3", "Ben", "555-0102"], [1003, "Mar 5", "Ana", "555-0101"]]
  }, {
    name: "Products",
    cols: ["product", "price"],
    key: [0],
    rows: [["Pen", "1.50"], ["Notebook", "4.00"], ["Mug", "8.00"]]
  }, {
    name: "OrderDetails",
    cols: ["orderID", "product", "qty"],
    key: [0, 1],
    fk: [0, 1],
    rows: [[1001, "Pen", 2], [1001, "Notebook", 1], [1002, "Mug", 1], [1003, "Notebook", 3]]
  }],
  counts: [2, 1],
  rule: "3NF: each column is about the key, not about another column.",
  problem: "The key is orderID, but phone is about another column: customer. A phone belongs to Ana, not to an order. So it repeats in every order Ana places: 1001 and 1003.",
  fix: "Move customers to their own table with a customerID. Orders keeps only the customerID."
}, {
  tab: "3NF",
  next: "Start over",
  tables: [{
    name: "Customers",
    cols: ["customerID", "name", "phone"],
    key: [0],
    rows: [[1, "Ana", "555-0101"], [2, "Ben", "555-0102"]]
  }, {
    name: "Orders",
    cols: ["orderID", "customerID", "date"],
    key: [0],
    fk: [1],
    rows: [[1001, 1, "Mar 2"], [1002, 2, "Mar 3"], [1003, 1, "Mar 5"]]
  }, {
    name: "Products",
    cols: ["product", "price"],
    key: [0],
    rows: [["Pen", "1.50"], ["Notebook", "4.00"], ["Mug", "8.00"]]
  }, {
    name: "OrderDetails",
    cols: ["orderID", "product", "qty"],
    key: [0, 1],
    fk: [0, 1],
    rows: [[1001, "Pen", 2], [1001, "Notebook", 1], [1002, "Mug", 1], [1003, "Notebook", 3]]
  }],
  counts: [1, 1],
  rule: "Done: every fact is stored once.",
  problem: null,
  fix: "To see an order in full, join the four tables back together with SQL."
}];

export const relView = ({v, acted}) => {
  const Table = relTable;
  return <div className="hdw-stack">
    <span className="hdw-eyebrow">{v.label}</span>
    <div className={v.tables.length > 1 ? "hdw-rdd-grid" : ""}>
      {v.tables.map((t, ti) => {
    const extra = acted ? v.after.filter(a => a.t === ti) : [];
    const rows = [...t.rows, ...extra.map(a => a.row)];
    return <div key={t.name}>
            <Table t={{
      ...t,
      rows: rows
    }} minWidth={v.tables.length > 1 ? 0 : undefined} rowCls={i => {
      if (i >= t.rows.length) return extra[i - t.rows.length].kind === "ghost" ? "is-ghost is-bad" : "is-new";
      return acted && v.mark && v.mark[0] === ti && v.mark[1] === i ? "is-bad" : "";
    }} />
          </div>;
  })}
    </div>
    <p className={`hdw-status${acted ? ` ${v.tone}` : ""}`}>{acted ? v.afterNote : v.note}</p>
  </div>;
};

export const RELS = [{
  tab: "One-to-many",
  shape: "Teachers (1) ──< Classes (many)",
  action: "Give Tan a 4th class",
  views: [{
    label: "Naive design",
    tables: [{
      name: "Teachers",
      cols: ["teacherID", "name", "class1", "class2", "class3"],
      key: [0],
      rows: [[1, "Tan", "MATH101", "PHYS101", "MATH201"], [2, "Lee", "CHEM101", null, null]]
    }],
    note: "One column per class. Lee leaves two cells empty.",
    after: [],
    mark: [0, 0],
    tone: "is-bad",
    afterNote: "Tan has no class4 column. You would have to change the table's design for every teacher, just for Tan."
  }, {
    label: "Split design",
    tables: [{
      name: "Teachers",
      cols: ["teacherID", "name"],
      key: [0],
      rows: [[1, "Tan"], [2, "Lee"]]
    }, {
      name: "Classes",
      cols: ["classID", "course", "teacherID"],
      key: [0],
      fk: [2],
      rows: [["C1", "MATH101", 1], ["C2", "PHYS101", 1], ["C3", "MATH201", 1], ["C4", "CHEM101", 2]]
    }],
    note: "Each class row stores its teacher. The foreign key sits on the “many” side.",
    after: [{
      t: 1,
      row: ["C5", "BIO101", 1],
      kind: "new"
    }],
    tone: "is-ok",
    afterNote: "One new row in Classes. No design change, and no empty cells."
  }]
}, {
  tab: "Many-to-many",
  shape: "Orders >──< Products, through OrderDetails",
  action: "Add a mug to order 1001",
  views: [{
    label: "Naive A: product column in Orders",
    tables: [{
      name: "Orders",
      cols: ["orderID", "customerID", "date", "product"],
      key: [0],
      fk: [3],
      rows: [[1001, 1, "Mar 2", "Pen"], [1002, 2, "Mar 3", "Mug"], [1003, 1, "Mar 5", "Notebook"]]
    }],
    note: "Each order has one product cell. Order 1001 also had a notebook, and there was nowhere to put it. The quantities are gone too.",
    after: [{
      t: 0,
      row: [1001, 1, "Mar 2", "Mug"],
      kind: "ghost"
    }],
    tone: "is-bad",
    afterNote: "Refused: a second row for order 1001 would repeat its key. Writing Mug over Pen would lose the pen instead."
  }, {
    label: "Naive B: orderID column in Products",
    tables: [{
      name: "Products",
      cols: ["product", "price", "orderID"],
      key: [0],
      fk: [2],
      rows: [["Pen", "1.50", 1001], ["Notebook", "4.00", 1003], ["Mug", "8.00", 1002]]
    }],
    note: "Each product has one order cell. The notebook was in orders 1001 and 1003, but only 1003 fits.",
    after: [{
      t: 0,
      row: ["Mug", "8.00", 1001],
      kind: "ghost"
    }],
    tone: "is-bad",
    afterNote: "Refused: the mug is already in order 1002, and a second Mug row would repeat its key. Changing 1002 to 1001 would take it out of Ben's order."
  }, {
    label: "Split design",
    tables: [{
      name: "Orders",
      cols: ["orderID", "customerID", "date"],
      key: [0],
      fk: [1],
      rows: [[1001, 1, "Mar 2"], [1002, 2, "Mar 3"], [1003, 1, "Mar 5"]]
    }, {
      name: "OrderDetails",
      cols: ["orderID", "product", "qty"],
      key: [0, 1],
      fk: [0, 1],
      rows: [[1001, "Pen", 2], [1001, "Notebook", 1], [1002, "Mug", 1], [1003, "Notebook", 3]]
    }, {
      name: "Products",
      cols: ["product", "price"],
      key: [0],
      rows: [["Pen", "1.50"], ["Notebook", "4.00"], ["Mug", "8.00"]]
    }],
    note: "OrderDetails has one row per order–product pair. Its key is both columns together.",
    after: [{
      t: 1,
      row: [1001, "Mug", 1],
      kind: "new"
    }],
    tone: "is-ok",
    afterNote: "One new row in OrderDetails. Order 1001 now has three products, and the mug is in two orders. That is the junction table at work: two one-to-many links, one from each side."
  }]
}, {
  tab: "One-to-one",
  shape: "Products (1) ── (1) ProductDetails",
  action: "Add a stapler with no photo",
  views: [{
    label: "Naive design",
    tables: [{
      name: "Products",
      cols: ["product", "price", "image", "longText"],
      key: [0],
      rows: [["Pen", "1.50", "pen.jpg", "Smooth gel ink…"], ["Notebook", "4.00", null, null], ["Mug", "8.00", "mug.jpg", "Holds 350 ml…"]]
    }],
    note: "Big, optional columns sit in every row, even where they are empty.",
    after: [{
      t: 0,
      row: ["Stapler", "12.00", null, null],
      kind: "new"
    }],
    tone: "is-bad",
    afterNote: "Stored, but with two more empty cells. A query that only needs prices still works with these wide rows."
  }, {
    label: "Split design",
    tables: [{
      name: "Products",
      cols: ["product", "price"],
      key: [0],
      rows: [["Pen", "1.50"], ["Notebook", "4.00"], ["Mug", "8.00"]]
    }, {
      name: "ProductDetails",
      cols: ["product", "image", "longText"],
      key: [0],
      fk: [0],
      rows: [["Pen", "pen.jpg", "Smooth gel ink…"], ["Mug", "mug.jpg", "Holds 350 ml…"]]
    }],
    note: "Details live in a second table with the same key. A product has at most one details row.",
    after: [{
      t: 0,
      row: ["Stapler", "12.00"],
      kind: "new"
    }],
    tone: "is-ok",
    afterNote: "One row in Products. No details row until there is a photo."
  }]
}];

export const CANDIDATES = [{
  label: "name",
  col: 1,
  res: ["fail", "pass", "pass", "pass"],
  why: ["Two rows now share the name Ana. The key can't tell them apart.", "Her name did not change.", "Her name did not change.", "The name is still there."],
  verdict: "Breaks on the first repeated name. Names are not unique."
}, {
  label: "phone",
  col: 2,
  res: ["pass", "fail", "pass", "fail"],
  why: ["The new Ana has a different number.", "The key itself changed. Every order that stored 555-0101 must change too.", "Her phone did not change.", "A primary key can never be empty (NULL)."],
  verdict: "Breaks twice. Phone numbers change, and some customers don't give one."
}, {
  label: "address",
  col: 3,
  res: ["pass", "pass", "fail", "pass"],
  why: ["The new Ana lives elsewhere. Two customers in one home would still break it.", "Her address did not change.", "The key changed. Every order that stored her address must change too.", "The address is still there."],
  verdict: "Breaks as soon as anyone moves."
}, {
  label: "customerID",
  col: 0,
  res: ["pass", "pass", "pass", "pass"],
  why: ["The database gives the new Ana ID 3.", "ID 1 stays. Only the phone column changes.", "ID 1 stays. Only the address column changes.", "The database assigns the ID, so it is never empty."],
  verdict: "Passes all four. A number with no real-world meaning never has a reason to change. This is called a surrogate key."
}];

export const EVENTS = ["A second Ana signs up", "Ana gets a new phone number", "Ana moves house", "A customer gives no phone"];

export const CUSTOMERS = {
  name: "Customers",
  cols: ["customerID", "name", "phone", "address"],
  rows: [[1, "Ana", "555-0101", "12 Elm St"], [2, "Ben", "555-0102", "4 Oak Rd"]]
};

export const ANOMALIES = [{
  label: "Change Ana's phone in row 1",
  kind: "Update anomaly",
  sql: "UPDATE OrderSheet SET phone = '555-0199'\nWHERE orderID = 1001 AND product = 'Pen';",
  rows: () => FLAT.map((r, i) => i === 0 ? [...r.slice(0, 3), "555-0199", ...r.slice(4)] : r),
  changed: [[0, 3]],
  bad: [1, 3],
  ghost: -1,
  note: "Rows 2 and 4 still say 555-0101. Ana now has two phone numbers, and nothing tells you which one is right.",
  fix: "In the split design you change one row in Customers."
}, {
  label: "Delete order 1002",
  kind: "Delete anomaly",
  sql: "DELETE FROM OrderSheet WHERE orderID = 1002;",
  rows: () => FLAT,
  changed: [],
  bad: [],
  ghost: 2,
  note: "Order 1002 was the only row about Ben and the only row about mugs. Deleting the order also deletes Ben's phone and the price of a mug.",
  fix: "In the split design Ben stays in Customers and the mug stays in Products."
}, {
  label: "Add a stapler priced 12.00",
  kind: "Insert anomaly",
  sql: "INSERT INTO OrderSheet (product, price)\nVALUES ('Stapler', 12.00);",
  rows: () => [...FLAT, [null, null, null, null, "Stapler", "12.00", null]],
  changed: [[4, 0]],
  bad: [],
  ghost: 4,
  note: "Each row is an order line, keyed by (orderID, product). Nobody has bought a stapler yet, so orderID would be NULL. A key can't be NULL, so the shop can't record the price.",
  fix: "In the split design you add one row to Products."
}];

export const sqlLine = ({text}) => text.split(new RegExp(SQL_KW.source, "g")).map((part, i) => i % 2 ? <span key={i} className="hdw-kw">{part}</span> : part);

export const SQL_KW = /\b(SELECT|SUM|FROM|JOIN|USING|WHERE|INSERT|INTO|VALUES|DELETE|UPDATE|SET|AND|NOT|NULL|CREATE|TABLE|REFERENCES|ON|CASCADE|RESTRICT|CHECK|PRIMARY|KEY)\b/;

export const relTable = ({t, rowCls = () => "", cellCls = () => "", headCls = () => "", minWidth, rowKey = (r, i) => String(i)}) => {
  const key = t.key || [];
  const fk = t.fk || [];
  const hot = t.hot || [];
  const join = (...c) => c.filter(Boolean).join(" ");
  return <div className="hdw-x">
      <table className="hdw-table hdw-rel" style={minWidth !== undefined ? {
    minWidth
  } : undefined}>
        <caption>{t.name}</caption>
        <thead>
          <tr>{t.cols.map((h, j) => <th key={h} scope="col" className={join(key.includes(j) && "is-key", hot.includes(j) && "is-hot", headCls(j))}>{h}</th>)}</tr>
        </thead>
        <tbody>
          {t.rows.map((r, i) => <tr key={rowKey(r, i)} className={rowCls(i, r)}>
              {r.map((v, j) => <td key={j} className={join(fk.includes(j) && "is-fk", hot.includes(j) && "is-hot", cellCls(i, j, r))}>{v === null ? <span className="hdw-null">NULL</span> : v}</td>)}
            </tr>)}
        </tbody>
      </table>
    </div>;
};

export const SPLIT = [{
  name: "Customers",
  cols: ["customerID", "name", "phone"],
  key: [0],
  fk: [],
  from: [2, 3],
  rows: [[1, "Ana", "555-0101"], [2, "Ben", "555-0102"]],
  note: "Ana's name and phone were typed three times. Now they are stored once."
}, {
  name: "Orders",
  cols: ["orderID", "customerID", "date"],
  key: [0],
  fk: [1],
  from: [0, 1],
  rows: [[1001, 1, "Mar 2"], [1002, 2, "Mar 3"], [1003, 1, "Mar 5"]],
  note: "Order 1001's date was repeated on each of its lines. Now each order has one row."
}, {
  name: "Products",
  cols: ["product", "price"],
  key: [0],
  fk: [],
  from: [4, 5],
  rows: [["Pen", "1.50"], ["Notebook", "4.00"], ["Mug", "8.00"]],
  note: "The notebook's price was typed twice. Now it is stored once."
}, {
  name: "OrderDetails",
  cols: ["orderID", "product", "qty"],
  key: [0, 1],
  fk: [0, 1],
  from: [0, 4, 6],
  rows: [[1001, "Pen", 2], [1001, "Notebook", 1], [1002, "Mug", 1], [1003, "Notebook", 3]],
  note: "What is left: which product, how many, in which order. Everything else is looked up."
}];

export const FLAT = [[1001, "Mar 2", "Ana", "555-0101", "Pen", "1.50", 2], [1001, "Mar 2", "Ana", "555-0101", "Notebook", "4.00", 1], [1002, "Mar 3", "Ben", "555-0102", "Mug", "8.00", 1], [1003, "Mar 5", "Ana", "555-0101", "Notebook", "4.00", 3]];

export const FLAT_COLS = ["orderID", "date", "customer", "phone", "product", "price", "qty"];

export const useOnceVisible = (ref, onEnter) => {
  const cb = useRef(onEnter);
  cb.current = onEnter;
  useEffect(() => {
    const el = ref.current;
    if (!el) return;
    if (typeof IntersectionObserver === "undefined") {
      cb.current();
      return;
    }
    const io = new IntersectionObserver(entries => {
      if (entries.some(e => e.isIntersecting)) {
        io.disconnect();
        cb.current();
      }
    }, {
      threshold: 0.35
    });
    io.observe(el);
    return () => io.disconnect();
  }, [ref]);
};

export const useReducedMotion = () => {
  const [reduce, setReduce] = useState(false);
  useEffect(() => {
    const mq = window.matchMedia("(prefers-reduced-motion: reduce)");
    setReduce(mq.matches);
    const on = e => setReduce(e.matches);
    mq.addEventListener("change", on);
    return () => mq.removeEventListener("change", on);
  }, []);
  return reduce;
};

> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Source: adapted from Chua Hock Chuan, ["Relational Database Design"](https://www3.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html) (Nanyang Technological University), with corrections.

A small shop keeps its orders in one sheet. Each line has the order number, the date, the customer, their phone, the product, its price and how many were bought. It works fine for a week. Then Ana changes her phone number, someone updates one row and forgets the other two, and the shop no longer knows how to reach her.

Nothing was typed wrong. The problem is the **design**: Ana's phone is stored in three places, so three places can disagree. **Database design** is deciding which tables you need, which columns go in each, and how the tables point at each other. The main goal is simple: **store each fact once**, so it can't be stored two different ways.

This post follows that shop from one sheet to four clean tables. It assumes you know what a table, a row and a key are. If not, read [The Relational Model](/writing/database/relational-model) first.

<Tip>
  **In short**

  * Storing a fact in several rows causes update, insert and delete anomalies.
  * Design in four steps: purpose, tables and keys, relationships, then normalize.
  * Pick a primary key that is unique, never empty and never changes. A plain auto-generated number (a surrogate key) is usually best.
  * One-to-many: put a foreign key on the "many" side. Many-to-many: add a junction table. One-to-one: split rarely used columns into a second table.
  * Normalize: 1NF means one value per cell. 2NF means columns depend on the whole key. 3NF means they depend on nothing but the key.
</Tip>

## One sheet, three kinds of bug

Here is the shop's sheet. Each row is one order line, so the key is the pair (orderID, product). Try three ordinary changes.

Starting sheet `OrderSheet`, key (orderID, product):

| orderID | date | customer | phone | product | price | qty |
| - | - | - | - | - | - | - |
| 1001 | Mar 2 | Ana | 555-0101 | Pen | 1.50 | 2 |
| 1001 | Mar 2 | Ana | 555-0101 | Notebook | 4.00 | 1 |
| 1002 | Mar 3 | Ben | 555-0102 | Mug | 8.00 | 1 |
| 1003 | Mar 5 | Ana | 555-0101 | Notebook | 4.00 | 3 |

| Change | Anomaly | What goes wrong | In the split design |
| - | - | - | - |
| `UPDATE OrderSheet SET phone = '555-0199' WHERE orderID = 1001 AND product = 'Pen';` | Update | Rows 2 and 4 still say 555-0101. Ana now has two phone numbers. | Change one row in `Customers`. |
| `DELETE FROM OrderSheet WHERE orderID = 1002;` | Delete | Order 1002 was the only row about Ben and about mugs. Ben's phone and the mug's price are lost too. | Ben stays in `Customers`; the mug stays in `Products`. |
| `INSERT INTO OrderSheet (product, price) VALUES ('Stapler', 12.00);` | Insert | No one has bought a stapler, so `orderID` would be NULL. A key can't be NULL, so the price can't be stored. | Add one row to `Products`. |

These three problems have names: **update**, **delete** and **insert anomalies**. They all have the same cause. The sheet mixes three subjects (customers, products and orders) in one table. Every fix in this post comes down to giving each subject its own table.

## The four steps

The design process has four steps. You will often loop back, but this is the order to start in.

1. **Decide the purpose.** What questions must the database answer? Sketch the forms people will fill in and the reports they will read. Anything a report needs must be stored somewhere.
2. **Split data into tables and pick primary keys.** One table per subject: customers, orders, products. Each table gets a key that picks out exactly one row.
3. **Connect the tables.** Work out how the subjects relate (one-to-many, many-to-many or one-to-one) and add foreign keys to match.
4. **Refine and normalize.** Check each table against the normal forms and split tables that still store a fact twice.

The rest of this post walks through steps 2 to 4 with the shop's data.

## Picking a primary key

A primary key has two hard rules: its value is **unique** for each row, and it is **never empty** (never NULL). There is a third rule, softer but just as important: it should **never change**. Other tables store copies of the key to point at a row. If the key changes, every copy must change too.

`Customers` has several columns that look unique today. Pick one and watch what normal shop life does to it.

`Customers`:

| customerID | name | phone | address |
| - | - | - | - |
| 1 | Ana | 555-0101 | 12 Elm St |
| 2 | Ben | 555-0102 | 4 Oak Rd |

Each column tried as the primary key against four events:

| Key | A second Ana signs up | Ana gets a new phone | Ana moves house | A customer gives no phone | Verdict |
| - | - | - | - | - | - |
| `name` | Breaks (two rows named Ana) | Works | Works | Works | Names are not unique |
| `phone` | Works | Breaks (key changes; every order pointing at it must change) | Works | Breaks (a key can't be NULL) | Phones change and can be missing |
| `address` | Works (breaks if two customers share a home) | Works | Breaks (key changes) | Works | Breaks when anyone moves |
| `customerID` | Works (new Ana gets ID 3) | Works (only the phone column changes) | Works | Works (the database assigns it) | Passes all four: a surrogate key |

`customerID` wins because it **means nothing**. It is just a number the database hands out, so no real-world event can make it wrong. A key like this is called a **surrogate key**. A key made from real data, such as an ISBN or an email address, is called a **natural key**.

Natural keys are fine when an outside authority guarantees they are unique and stable, like an ISBN for a book. When in doubt, use a surrogate. Most databases can generate one for you: `AUTO_INCREMENT` in MySQL, `GENERATED ... AS IDENTITY` in PostgreSQL and SQL Server. These use a counter, not "the largest current value plus one". So after you delete row 7, the next row usually gets 8, not 7 again. Numbers can have gaps, and that is fine; a key only has to be unique.

Keep keys small, too. A one-column number is quicker to compare and index than three text columns, and every table that points at it stores a copy.

## Connecting tables

Once each subject has its own table, you need to record how the subjects relate. There are three shapes. Each one has a naive design that seems to work and a split design that holds up. Try the same change on each.

**One-to-many: a teacher teaches many classes; each class has one teacher.**

Naive design, one column per class:

| teacherID | name | class1 | class2 | class3 |
| - | - | - | - | - |
| 1 | Tan | MATH101 | PHYS101 | MATH201 |
| 2 | Lee | CHEM101 | NULL | NULL |

Giving Tan a 4th class needs a new `class4` column, a design change for every teacher. Lee already wastes two cells.

Split design, foreign key on the "many" side (`Classes.teacherID` → `Teachers.teacherID`):

| teacherID | name |
| - | - |
| 1 | Tan |
| 2 | Lee |

| classID | course | teacherID |
| - | - | - |
| C1 | MATH101 | 1 |
| C2 | PHYS101 | 1 |
| C3 | MATH201 | 1 |
| C4 | CHEM101 | 2 |

Tan's 4th class is one new row: `('C5', 'BIO101', 1)`.

**Many-to-many: an order holds many products; a product appears in many orders.**

The one-to-many trick (one foreign key) fails from both sides. The test: add a mug to order 1001. The mug is already in order 1002.

Naive A, a `product` column in `Orders`:

| orderID | customerID | date | product |
| - | - | - | - |
| 1001 | 1 | Mar 2 | Pen |
| 1002 | 2 | Mar 3 | Mug |
| 1003 | 1 | Mar 5 | Notebook |

Each order fits one product, so order 1001's notebook and every quantity are already lost. Adding the mug needs a second row for order 1001, which repeats its key and is refused. Overwriting `Pen` would lose the pen.

Naive B, an `orderID` column in `Products`:

| product | price | orderID |
| - | - | - |
| Pen | 1.50 | 1001 |
| Notebook | 4.00 | 1003 |
| Mug | 8.00 | 1002 |

Each product fits one order, so the notebook can't be in both 1001 and 1003. Adding the mug to 1001 needs a second `Mug` row, which repeats its key and is refused. Changing 1002 to 1001 would take the mug out of Ben's order.

Split design, a junction table `OrderDetails` with key (orderID, product). Both columns are foreign keys:

| orderID | product | qty |
| - | - | - |
| 1001 | Pen | 2 |
| 1001 | Notebook | 1 |
| 1002 | Mug | 1 |
| 1003 | Notebook | 3 |

Adding a mug to order 1001 is one new row: `(1001, 'Mug', 1)`. Now order 1001 has three products and the mug is in two orders. The many-to-many becomes two one-to-many links: `Orders` 1 → many `OrderDetails`, and `Products` 1 → many `OrderDetails`.

**One-to-one: a product has at most one details record.**

Naive design: `Products(product, price, image, longText)`, where `image` and `longText` are empty for many products.

Split design: `Products(product, price)` and `ProductDetails(product, image, longText)`, both keyed by `product`. Only products with a photo get a details row. Adding a stapler with no photo is one row in `Products`.

**One-to-many** is the most common shape. One customer places many orders; one teacher teaches many classes. Put the foreign key on the "many" side: each class row stores its teacher. Never put a list on the "one" side. A row can't hold a growing list without adding columns.

**Many-to-many** can't be stored with one foreign key, because neither side has a single partner. A `product` column in `Orders` allows one product per order; an `orderID` column in `Products` allows one order per product. Either way, the "many" on the other side has nowhere to go. Add a third table, called a **junction table**, with one row per pair. Its primary key is usually both foreign keys together. It can also hold facts about the pair itself, such as `qty`.

**One-to-one** is rare. Use it to move columns that are large, often empty or sensitive into their own table, for example product photos or an employee's salary. The second table uses the same primary key as the first, and that key is also its foreign key.

## Normalizing, one rule at a time

**Normalization** is a checklist for finding facts that are still stored twice. Each **normal form** is one rule. A table in 2NF also follows 1NF, and a table in 3NF follows both. Step through the shop's data from a raw order list to 3NF.

**Raw (not normalized).** `items` holds a list:

| orderID | date | customer | phone | items |
| - | - | - | - | - |
| 1001 | Mar 2 | Ana | 555-0101 | Pen ×2 @1.50, Notebook ×1 @4.00 |
| 1002 | Mar 3 | Ben | 555-0102 | Mug ×1 @8.00 |
| 1003 | Mar 5 | Ana | 555-0101 | Notebook ×3 @4.00 |

Rule broken: **1NF**, one value per cell. The `items` cell holds more than one value: order 1001's cell has a pen and a notebook. So counting notebooks sold means splitting text by hand. Fix: give each product in an order its own row. Now a row is found by two things: the order number and the product, so the key is (orderID, product).

**1NF.** `OrderLines`, key (orderID, product). This is the `OrderSheet` table above. Ana's phone now appears three times.

Rule broken: **2NF**, each column needs the whole key, not just half of it. The key has two halves: `orderID` and `product`. `date`, `customer` and `phone` need only the `orderID` half: order 1001 is on Mar 2 for Ana, whatever the product. `price` needs only the `product` half: a notebook costs 4.00 in any order. Because they use only half the key, they repeat on every row that shares that half. Fix: move `date`, `customer` and `phone` to `Orders`, and `price` to `Products`. `OrderLines` keeps only `qty` (how many of this product in this order), the one fact that needs both halves.

**2NF.**

`Orders(orderID, date, customer, phone)`:

| orderID | date | customer | phone |
| - | - | - | - |
| 1001 | Mar 2 | Ana | 555-0101 |
| 1002 | Mar 3 | Ben | 555-0102 |
| 1003 | Mar 5 | Ana | 555-0101 |

`Products(product, price)`: Pen 1.50, Notebook 4.00, Mug 8.00.

`OrderDetails(orderID, product, qty)`: (1001, Pen, 2), (1001, Notebook, 1), (1002, Mug, 1), (1003, Notebook, 3).

Rule broken: **3NF**, each column is about the key, not about another column. The key is `orderID`, but `phone` is about another column: `customer`. A phone belongs to Ana, not to an order, so it repeats in every order Ana places (1001 and 1003). Fix: move customers to their own table with a `customerID`, and keep only `customerID` in `Orders`.

**3NF.** The final design:

| Table | Columns (key first) | Rows |
| - | - | - |
| `Customers` | customerID, name, phone | (1, Ana, 555-0101), (2, Ben, 555-0102) |
| `Orders` | orderID, customerID → Customers, date | (1001, 1, Mar 2), (1002, 2, Mar 3), (1003, 1, Mar 5) |
| `Products` | product, price | (Pen, 1.50), (Notebook, 4.00), (Mug, 8.00) |
| `OrderDetails` | (orderID → Orders, product → Products), qty | (1001, Pen, 2), (1001, Notebook, 1), (1002, Mug, 1), (1003, Notebook, 3) |

How many times each fact is stored:

| Stage | Ana's phone | Notebook's price |
| - | - | - |
| Raw | 2 | 2 |
| 1NF | 3 | 2 |
| 2NF | 2 | 1 |
| 3NF | 1 | 1 |

Notice that 1NF made things **worse** before they got better: Ana's phone went from two copies to three. 1NF only makes the data queryable. 2NF and 3NF remove the copies.

Here are the three rules in plain words:

* **First normal form (1NF):** one value per cell. No lists in a cell and no `item1`, `item2`, `item3` columns.
* **Second normal form (2NF):** each column needs the whole key, not just half of it. Order date needs only the order number, so it doesn't belong in a table keyed by order *and* product. This rule only matters when the key has two or more columns.
* **Third normal form (3NF):** each column is about the key, not about another column. A phone is about the customer, so it doesn't belong in `Orders`.

A short way to remember all three: every column describes the key, the whole key, and nothing but the key.

`Products` keeps the product name as its key to keep this example short. A real shop would add a `productID` for the same reason it added `customerID`: names get corrected and renamed.

### Wait, shouldn't the price stay with the order?

2NF moved `price` to `Products`. That is right for **today's price**. But if the mug costs 9.00 next month, order 1002 should still show that Ben paid 8.00. "The price Ben paid" is a different fact from "the price of a mug", and it depends on the whole key (this order, this product). So real systems usually keep **both**: `Products.price` for the current price, and `OrderDetails.priceAtOrder` for history. Normalization doesn't forbid this. Each column stores a different fact once.

### Beyond 3NF

3NF has a blind spot: it only checks the columns **outside** the key. A repeated fact can hide **inside** the key, and 3NF won't see it. **Boyce–Codd normal form (BCNF)** closes the gap with one rule: **if one column tells you another, it must be a key.**

Here is a school that keeps one table of who studies what with whom. It follows two rules:

1. A student takes each course with one teacher.
2. Each teacher teaches only one course. A course can have several teachers.

Step through the two checks.

`Teaching(student, course, teacher)`. Rule 1 makes (student, course) the key. Rule 2: each teacher teaches one course, and MATH101 has two teachers (Tan and Kim).

| student | course | teacher |
| - | - | - |
| Ana | MATH101 | Tan |
| Ben | MATH101 | Tan |
| Dan | MATH101 | Tan |
| Chi | MATH101 | Kim |
| Ana | PHYS101 | Lee |

| Check | Question | Answer | Result |
| - | - | - | - |
| 3NF | Is every column outside the key about the key? | Only `teacher` is outside the key, and student + course tells you the teacher. `course` is inside the key, so 3NF never checks it. | Passes |
| BCNF | If one column tells you another, is it a key? | `teacher` tells you `course` (Tan → MATH101), but `teacher` is not a key: Tan is on three rows. | Fails |

The cost: "Tan teaches MATH101" is stored 3 times. Moving Tan to MATH201 means changing 3 rows; missing one leaves Tan teaching two courses.

BCNF split: `TeacherCourse(teacher, course)` keyed by `teacher`: (Tan, MATH101), (Kim, MATH101), (Lee, PHYS101). `Enrolment(student, teacher)` keyed by both: (Ana, Tan), (Ben, Tan), (Dan, Tan), (Chi, Kim), (Ana, Lee). The fact is stored once; moving Tan changes 1 row.

You will rarely hit this case. It needs a table where two different column pairs could each be the key and they share a column: here (student, course) and (student, teacher) both pick out one row. Most tables that pass 3NF also pass BCNF, so many designers simply aim for BCNF.

The fourth and fifth normal forms deal with tables that combine several independent lists, such as a person's skills and languages in one table. You will rarely meet them if each table describes one subject.

## Breaking the rules on purpose

A normalized design stores each fact once, but answering a question can take several joins. Sometimes a design copies a fact on purpose to make reads faster or simpler. This is called **denormalization**.

For example, the shop could store `totalPrice` in `Orders` so the order list doesn't add up `OrderDetails` every time. Try it on order 1001: 2 pens at 1.50 and 1 notebook at 4.00.

Order 1001: 2 pens at 1.50 and 1 notebook at 4.00, so the total is 7.00.

| Action | Normalized (total worked out on read) | Denormalized (`Orders.totalPrice` stored) |
| - | - | - |
| Read the total | `SELECT SUM(d.qty * p.price) FROM OrderDetails d JOIN Products p USING (product) WHERE d.orderID = 1001;` reads 4 rows | `SELECT totalPrice FROM Orders WHERE orderID = 1001;` reads 1 row |
| Change pens to 5 | 1 write; the next read gives 11.50 | 2 writes: the line and `totalPrice = 11.50` |
| Change pens to 5, skip the total | 1 write; still correct | 1 write; `totalPrice` stays 7.00 while the lines add up to 11.50 |

The stored total is a second copy of a fact, and every change to an order line must update it too. So:

* Denormalize only after you measure a real slowdown, not "just in case".
* Write down which column is a copy and what keeps it in sync, such as a trigger, the application, or a nightly job.
* Consider a view or a materialized view first. It gives the fast read without a hand-maintained copy.

## Rules the database enforces for you

Good tables are half the job. The other half is writing the rules into the table definition, so the database rejects bad data on every write. You write each rule once; no application can skip it. Try some writes against `OrderDetails`.

```sql theme={null}
CREATE TABLE OrderDetails (
  orderID INT NOT NULL REFERENCES Orders ON DELETE RESTRICT,
  product TEXT NOT NULL REFERENCES Products,
  qty INT CHECK (qty > 0),
  PRIMARY KEY (orderID, product)
);
```

Starting rows: (1001, Pen, 2), (1001, Notebook, 1), (1002, Mug, 1), (1003, Notebook, 3).

| Write | Result | Rule that decides |
| - | - | - |
| Insert (1003, Pen, 0) | Rejected | `CHECK (qty > 0)` |
| Insert (1003, Stapler, 1) | Rejected | `REFERENCES Products`: no Stapler row |
| Insert (NULL, Mug, 1) | Rejected | `NOT NULL`: orderID is part of the primary key |
| Insert (1001, Pen, 3) | Rejected | `PRIMARY KEY`: order 1001 already has a Pen line |
| Delete order 1001 with `ON DELETE RESTRICT` | Rejected | Two lines still point at it |
| Delete order 1001 with `ON DELETE CASCADE` | Accepted | The order and its two lines are deleted together |
| Insert (1003, Mug, 1) | Accepted | Every rule passes |

The rules fall into three groups. [The Relational Model](/writing/database/relational-model#constraints-rules-on-every-write) covers them in more detail.

* **Entity integrity:** no part of a primary key may be NULL.
* **Referential integrity:** every foreign key points at a row that exists. When a parent row is deleted, you choose what happens to the rows pointing at it: refuse (`RESTRICT`), delete them too (`CASCADE`), or empty their foreign key (`SET NULL`). `SET NULL` only works on a foreign key that may be empty, so it can't be used on `OrderDetails.orderID`, which is part of the key.
* **Business rules:** facts specific to your shop, such as "quantity is more than 0". Use `CHECK`, `NOT NULL` and `UNIQUE`, plus column types that fit the data: whole numbers, decimals for money, dates for dates.

### Indexes

Last, add **indexes** for the columns you search and join on. An index is a sorted copy of one column that points back at the rows, like the index at the back of a book. It speeds up reads and slows down writes.

`Customers` holds 8 rows in arrival order; two are named Ana (IDs 2 and 8).

| Job | No index | Index on `name` |
| - | - | - |
| `SELECT * FROM Customers WHERE name = 'Ana';` | Reads all 8 rows, because any could be an Ana | A few steps through the sorted names, then reads only rows 2 and 8 |
| `INSERT INTO Customers VALUES (9, 'Dan');` | 1 write | 2 writes: the row, plus an index entry in its sorted place |
| `DELETE FROM Customers WHERE customerID = 9;` | 1 write | 2 writes: the row and its index entry |

With a million rows, a lookup through the index takes about 20 steps instead of a million reads.

Primary keys are indexed automatically in most databases; foreign keys often are not. When a row changes, the database updates that row's index entry; it does not rebuild the whole index. Still, every index adds work to every write, so index for the queries you actually run.

## Summary

Database design comes down to four steps. Here they are on one screen, with the rule for each and what it meant for the shop.

| Step | Do | Rule | In the shop |
| - | - | - | - |
| 1. Decide the purpose | List the questions people will ask and the reports they will read. | Anything a report needs must be stored somewhere. | Who ordered what, when, and for how much. |
| 2. Split into tables, pick keys | One table per subject. Give each a primary key. | A key is unique, never empty and never changes. Use a meaningless ID. | `Customers`, `Orders`, `Products`, keyed by `customerID`, `orderID`, `product`. |
| 3. Connect the tables | For each pair of subjects, decide: one-to-many, many-to-many or one-to-one. | Foreign key on the "many" side. Many-to-many gets a junction table. One-to-one shares the key. | `Orders` → `Customers`. `OrderDetails` links `Orders` and `Products`. |
| 4. Normalize | Check every table against the normal forms, and split where a fact repeats. | 1NF one value per cell. 2NF the whole key. 3NF nothing but the key. BCNF: what decides a column is a key. | One messy sheet became four tables; each fact is stored once. |

Expect to loop: normalizing often shows a missing table or key.

What matters most:

1. **Store each fact once.** A fact stored twice will one day disagree with itself. That is what update, insert and delete anomalies are.
2. **Keys never change.** Use a meaningless ID, so no real-world event can break what points at it.
3. **Let the database enforce the rules.** Primary keys, foreign keys, `NOT NULL` and `CHECK` stop bad data on every write, from every app.
4. **Copy data only on purpose.** Denormalize after you measure a slow read, and write down what keeps the copy in sync.
5. **Index what you search.** Index the columns you filter and join on, not every column. Each index costs every write.

## Check yourself

Questions this document answers:

* What are update, insert and delete anomalies? Problems caused by storing one fact in many rows. Update: changing Ana's phone in one row leaves old copies in others. Delete: deleting Ben's only order also loses Ben and the mug's price. Insert: a new product can't be stored until someone orders it, because `orderID` is part of the key.
* What are the steps of relational database design? Define the purpose, split data into tables with primary keys, create relationships, then refine and normalize.
* What makes a good primary key? Unique, never NULL, never changing, and small. A surrogate key (an auto-generated number with no meaning) meets all four.
* Does auto-increment reuse deleted numbers? Usually not. It uses a counter or sequence, so gaps are normal.
* Where does the foreign key go in a one-to-many relationship? On the "many" side: `Classes.teacherID` points at `Teachers`.
* How do you store a many-to-many relationship? With a junction table such as `OrderDetails(orderID, product, qty)`, whose key is both foreign keys together.
* When is a one-to-one relationship useful? To split large, optional or sensitive columns into a second table that shares the same primary key.
* What do 1NF, 2NF and 3NF require? 1NF: one value per cell, no repeating groups. 2NF: 1NF, and every non-key column depends on the whole key. 3NF: 2NF, and no non-key column depends on another non-key column.
* What is BCNF? A stricter 3NF: every column that determines another column must be a candidate key.
* Should an order line store the product's price? Yes, if you need the price paid at the time. That is a different fact from the current price in `Products`.
* What is denormalization? Storing a copy of a fact on purpose, such as `Orders.totalPrice`, to speed up reads. It needs a documented way to keep the copy in sync.
* Do indexes get rebuilt on every write? No. The changed entries are updated, which still costs time on every insert, update and delete.

## Glossary

| Term | Meaning |
| - | - |
| Anomaly | A bug caused by storing one fact in several places: update, insert or delete. |
| Primary key | The column(s) that pick out exactly one row. Unique and never NULL. |
| Surrogate key | A primary key with no real-world meaning, usually an auto-generated number. |
| Natural key | A primary key made from real data, such as an ISBN. |
| Composite key | A key made of two or more columns, such as (orderID, product). |
| Foreign key | A column whose values must match a key in another table. |
| One-to-many | One row on one side relates to many on the other. Foreign key goes on the "many" side. |
| Many-to-many | Many rows relate to many rows. Stored with a junction table. |
| Junction table | A table with one row per related pair, keyed by both foreign keys. |
| One-to-one | Each row relates to at most one row in another table that shares its key. |
| Normalization | Splitting tables so each fact is stored once. |
| 1NF | One value per cell; no repeating groups. |
| 2NF | 1NF, and every non-key column depends on the whole key. |
| 3NF | 2NF, and no non-key column depends on another non-key column. |
| BCNF | Every column that decides another column is a candidate key. |
| Denormalization | Storing a copy of a fact on purpose, for faster reads. |
| Index | A lookup structure that speeds up reads and slows down writes. |

## For AI agents

You are reading the Markdown export of `/writing/database/relational-database-design`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/relational-model` (relations, keys, constraints) and `/writing/database/what-is-a-database`.

***

Adapted from Chua Hock Chuan, ["Relational Database Design"](https://www3.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html), with corrections on index maintenance and auto-increment, a clearer 3NF example, and a note on storing the price at order time. The shop data is illustrative; figures are simplified teaching models.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.