Database Design Basics — One Fact, One Place

The first sketch of ReadingRoom was one wide table, like a spreadsheet: every loan row carried the member's name and email and the book's title and author. It worked until a member changed their email and half their rows still showed the old one. Design is about making that kind of mistake impossible.

The rule

Store each fact once; point to it with a key.

Three ideas carry most of the weight:

• A primary key uniquely identifies each row, usually an Id column. It never changes and never repeats.
• A foreign key is a column holding another table's primary key: Loans.MemberId points at Members.Id.
• Normalisation means splitting data so each fact lives in exactly one row. If you see the same value copied down a column, it probably belongs in its own table.

Before and after:

becomes Members(Id, Name, Email), Books(Id, Title, Author) and Loans(Id, MemberId, BookId, DueDate). Asha's email now lives in one row.

Relationships come in three shapes:

• One-to-many — one member has many loans. The foreign key sits on the "many" side (Loans.MemberId).
• Many-to-many — members borrow many books; books go to many members. A middle table (Loans) holds pairs of keys.
• One-to-one — a member has one library card record. Rare; often just columns on the same table.

The shortcut

For every column, ask "is this a fact about the row's own thing?" An email is a fact about a member, not about a loan, so it moves to Members, and the loan keeps only MemberId.

The trap: over-splitting. A due date is a fact about the loan itself, so it stays on Loans.

Drills

Answer fast — trust the rule.

Remember

• Store each fact once; point to it with a key.
• Ask of every column: is this a fact about this row's own thing?
• Foreign keys sit on the many side; many-to-many needs a middle table.