Database design

Conceptual — the entities and relationships the organisation cares about. Logical — those turned into tables, columns, keys and data types. Physical — how the chosen system actually stores it, including files and indexes.

It contains the entities and the relationships between them, in language the organisation recognises. It deliberately omits keys, data types and any choice of database product.

How the chosen system stores the data: files on disk, indexes, partitions and storage settings. This is where performance decisions live, and it must be redone if you change database product.

The schema levels are insulated from each other, so a change at one does not force a change at another. Adding an index at the physical level alters no query written against the logical schema.

So the meaning can be agreed with non-technical people before technical choices are made, so storage can change without breaking programs, and so the same logical design can be built on different database systems.

The entities a database stores and the relationships between them. Each box is one kind of thing that will become a table; each line is a relationship, labelled with the verb joining them.

Cardinality is how many — one or many, drawn as a bar or a crow's foot. Modality is whether the relationship is compulsory — mandatory drawn as a second bar, optional as a circle.

Say it as a sentence in both directions. 'A teacher teaches many classes' and 'a class is taught by one teacher' together give one-to-many, with the crow's foot on the Class end.

A column holds only one value, so neither table can hold several foreign keys. It is replaced by a linking entity with one row per pair, keyed on both foreign keys together.

In the table on the many side. A Class holds TeacherID, because the alternative would need a Teacher row to hold an unknown number of class columns.

It says what kind of value may be stored, and the database refuses anything that does not fit. It also decides what can be done with the values — arithmetic on numbers, true ordering on dates.

Ask whether you will ever calculate with it, compare it, or sort it. If yes it needs a real type; if it is only ever displayed — a phone number or postcode — text is correct however numeric it looks.

A numeric type drops the leading zero, so 07700 becomes 7700, and no arithmetic is ever done on a phone number anyway.

Sorting compares character by character so the order is wrong; date ranges and age calculations become impossible; and nothing rejects an impossible date such as 31/02/2026.

Otherwise the join either fails or silently returns nothing — values that look equal to a person are not equal to the database when their types differ.

Each entity becomes a table and each relationship becomes a foreign key. In a one-to-many the foreign key goes in the many table; a many-to-many needs a linking table holding both foreign keys.

Because a column holds one value. A Class row holds one TeacherID easily, whereas a Teacher row would need an unknown number of columns to hold every class they take.

A composite key uses two or more columns together as the primary key, keeping them separate and queryable. A concatenated key glues values into a single column, such as 2026-S1, which then has to be pulled apart to filter on either part.

Entity integrity: a primary key is never empty or duplicated. Referential integrity: a foreign key must match an existing row. Domain integrity: a value must fit its column's data type.

Rules enforced by the database apply to every program that touches the data, including ones written years later. Rules enforced in a program apply only to that program.

One column's value determining another's. Knowing a StudentID tells you the student's name, so Name is functionally dependent on StudentID. Normal forms are rules about which dependencies are allowed where.

1NF: every cell holds one value and every row is uniquely identified. 2NF: no non-key column depends on only part of a composite key. 3NF: no non-key column determines another non-key column.

A non-key column depending on only part of a composite primary key. With a key of StudentID and Club, StudentName depends on StudentID alone. It can only arise when the key is composite.

A non-key column determining another non-key column. Club determines Teacher and Teacher determines Room, so Room depends on Club only indirectly — which breaks 3NF.

To prevent update problems, where one copy of a repeated fact is changed and others are not; insert problems, where a new club with no members has nowhere to live; and delete problems, where removing the last member loses the club's details too.

List every piece of data with sample rows; make cells atomic and choose the key; move out anything depending on part of a composite key; move out any non-key column determined by another non-key column.

Because functional dependencies are far easier to see in actual data than in a list of column names — repeated values make the repeated facts visible.

In each table ask whether any non-key column determines another non-key column. If so, both move to a new table, and the determining column stays behind as a foreign key.

Pick a fact the scenario needs and follow the keys to find it. If you cannot reach it, a link is missing; if you find it in two places, the design is not yet in 3NF.

More than feels natural — a scenario with eight columns usually becomes about four tables. Finishing with one or two almost always means a transitive dependency was missed.

Deliberately storing some data more than once so that reads need fewer joins and run faster. It is a considered engineering trade, not a mistake.

It gains faster reads and simpler queries. It risks the same fact being stored in two places and disagreeing, requires every update to find every copy, and brings back insert and delete problems.

When data is read far more often than it is written — reports, dashboards and data warehouses that are loaded in bulk and then only read. Never for frequently-written data such as bookings or stock levels.

Keep the normalised tables as the authoritative source and rebuild the denormalised copy from them, so there is always one correct version to fall back on.

Because you should design in 3NF, run it, and find out where it is actually slow. Denormalising before a measured problem exists gives away correctness for a speed gain you cannot demonstrate.