P-6.4 Designing a Schema That Holds Up
You can design tables from a description of a problem, apply normalisation up to third normal form, and say when denormalising is a deliberate trade rather than a mistake.
Normalisation has a reputation for being theoretical, and the underlying idea is entirely practical: store each fact once, so it cannot be updated in one place and left stale in another. Third normal form is where the returns level off for most applications. Denormalising deliberately for read performance is legitimate, and the difference between that and a badly designed schema is whether you can say what you traded and why.
Work through these
Turning a description of a problem into tables and keys
Nouns tend to become tables and relationships between them become keys, which gets you most of a first draft. The draft is then tested against the questions the data must answer.
Repeated data is a defect waiting to happen
The same fact stored in two rows will eventually be updated in one of them. Every normal form is a different way of saying that.
NPTEL: Data Base Management System · CourseFirst, second and third normal form, in plain terms
One value per cell, no column depending on part of a composite key, and no column depending on another non-key column. Most real schemas stop here and are right to.
Constraints: letting the database refuse bad data
Uniqueness, required values, allowed ranges and referential rules belong in the schema rather than in every application that writes to it. A rule enforced in one place cannot be forgotten in another.
Denormalising on purpose, and recording why
Duplicating data to make a common read cheaper is a legitimate trade against update complexity. What makes it engineering rather than sloppiness is writing down the reason next to the schema.
Sign in to keep your progress.
Free resources
Links last checked 31 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.