Data & Databases

Database Design and Normalization: From Entities to BCNF

Beginner8 min readUpdated

Normalization is not academic ceremony: it is a mechanical way to remove the redundancy that causes update anomalies. This guide models one small schema, derives its functional dependencies and walks it from an unnormalised spreadsheet to BCNF, then covers surrogate versus natural keys, referential integrity, deliberate denormalisation and the schema smells that signal a design in trouble.

What you will learn

  • Identify entities, relationships, candidate keys and cardinalities from a description of a business domain
  • Write functional dependencies and use them to detect redundancy
  • Take one table from unnormalised form through 1NF, 2NF, 3NF and BCNF
  • Choose between surrogate and natural keys and enforce integrity with constraints
  • Decide when denormalisation is justified and how to keep it safe

Database design is where a project's future problems are decided. An application can be rewritten in a weekend; a schema that has been in production for three years, with millions of rows and a dozen consumers, cannot. The good news is that most of the discipline is teachable and mechanical: describe the domain honestly, write down which values determine which other values, and let those dependencies tell you where the tables should be split.

This guide works one small example all the way through. The domain is a conference: attendees sign up for sessions, sessions happen in rooms, and speakers present on topics. It is deliberately mundane, because the interesting part is not the domain but the redundancy that appears when you record it carelessly.

Entities, relationships and keys

Start by naming the things the business talks about. Each noun that has an independent existence and its own attributes is a candidate entity: attendee, session, room, speaker. Each verb that connects them is a relationship: an attendee signs up for a session; a session is held in a room.

The relationship's cardinality decides how it is stored:

  • One-to-many — a room hosts many sessions, a session sits in one room. Put the foreign key on the many side: session.room_id.
  • Many-to-many — an attendee signs up for many sessions, a session has many attendees. This needs its own table, usually called a junction or associative table, whose primary key is the pair of foreign keys. Attributes of the relationship itself (the time of signup, whether the attendee showed up) belong there too.
  • One-to-one — genuinely rare. It usually means two entities that could be one table, or an optional extension you want to keep separate for size or access-control reasons.

A candidate key is a minimal set of columns whose values are unique across every row and never null. A table can have several; the one you choose to be the primary key is a matter of convenience, not truth. attendee.email and attendee.badge_number might both be candidate keys.

Two words are worth defining early because normalization is stated in terms of them. A column is a prime attribute if it belongs to some candidate key, and a non-prime attribute otherwise. Every normal form below is a rule about how non-prime attributes are allowed to depend on prime ones.

Functional dependencies

A functional dependency written A → B means: any two rows that agree on A must agree on B. It is a statement about the business, not about the data you happen to have. "Two sessions in the same room have the same room capacity" is a dependency; "no two attendees currently share a surname" is a coincidence.

Suppose someone hands you this spreadsheet, exported as a single table:

attendee_email | attendee_name | session_code | session_title | room | room_capacity | starts_at           | topics
[email protected]| Ana Ruiz      | S12          | Query plans   | R2   | 120           | 2026-05-04 09:00    | sql, performance
[email protected]| Ana Ruiz      | S19          | Sharding      | R1   | 300           | 2026-05-04 11:00    | nosql, scaling
[email protected]| Bob Neri      | S12          | Query plans   | R2   | 120           | 2026-05-04 09:00    | sql, performance

The dependencies the business confirms are:

attendee_email                -> attendee_name
session_code                  -> session_title, room, starts_at
room                          -> room_capacity
(attendee_email, session_code)-> everything (the composite key)

Redundancy is now visible as a rule, not a hunch: because session_code → room, every extra signup for S12 repeats "R2" and "120". That repetition is what causes the three classic anomalies. An update anomaly: renaming a session means editing many rows, and missing one leaves the table contradicting itself. An insertion anomaly: you cannot record a room's capacity until somebody signs up for a session held there. A deletion anomaly: cancelling the last signup for S12 erases the fact that S12 exists at all.

Working the normal forms

Each normal form removes one class of dependency. Reaching a form means you also satisfy every form below it.

First normal form

1NF requires every column to hold a single, atomic value of one type, and forbids repeating groups. The topics column fails: it packs a list into a string, so you cannot index it, join on it, constrain it or count topics without parsing text. Columns named phone1, phone2, phone3 fail the same test in the other direction.

The fix is a table:

CREATE TABLE session_topic (
  session_code  varchar(8) NOT NULL REFERENCES session(code) ON DELETE CASCADE,
  topic         varchar(40) NOT NULL,
  PRIMARY KEY (session_code, topic)
);

A common question: does a jsonb or array column break 1NF? Formally yes, practically it depends. Storing genuinely opaque, variable payloads in one document column is a reasonable engineering choice; storing structured data you routinely filter and join on is 1NF avoidance with extra steps.

Second normal form

2NF applies only when the primary key is composite. It forbids a partial dependency: a non-prime attribute that depends on part of the key rather than all of it. Here the key is (attendee_email, session_code), yet session_title depends on session_code alone and attendee_name on attendee_email alone.

Splitting on those dependencies yields three tables — attendee, session and the signup junction — and the junction keeps only what depends on the whole key, such as signed_up_at.

Third normal form

3NF forbids a transitive dependency: a non-prime attribute determined by another non-prime attribute. In the session table, session_code → room and room → room_capacity, so capacity is transitively dependent on the key. Move it:

CREATE TABLE room (
  code      varchar(8) PRIMARY KEY,
  capacity  integer NOT NULL CHECK (capacity > 0)
);

CREATE TABLE session (
  code      varchar(8) PRIMARY KEY,
  title     varchar(120) NOT NULL,
  room_code varchar(8) NOT NULL REFERENCES room(code),
  starts_at timestamptz NOT NULL
);

Capacity is now stored once. Correcting it is a single-row update that cannot leave the database inconsistent.

Boyce-Codd normal form

BCNF tightens 3NF: every determinant must be a candidate key, including determinants of prime attributes. The difference shows up only when candidate keys overlap.

Take a table recording which speaker covers which topic in a session, under a rule that a given speaker only ever covers one topic:

speaker_assignment(session_code, topic, speaker)
  (session_code, topic)   -> speaker      -- candidate key
  (session_code, speaker) -> topic        -- candidate key
  speaker                 -> topic        -- determinant that is NOT a key

The table is in 3NF — topic is prime, so there is no transitive dependency on a non-prime attribute — yet the speaker-to-topic fact repeats for every session that speaker appears in. Decompose into speaker(name, topic) and session_speaker(session_code, speaker). Note the trade-off honestly: this decomposition is lossless but does not preserve the original composite key as a single-table constraint, which is one reason 3NF is sometimes the pragmatic stopping point.

Fourth normal form goes one step further and separates independent multi-valued facts. If a session had both a list of topics and an unrelated list of sponsors, cramming both into one table would produce a row for every combination. Splitting them into two tables is 4NF, and it is mostly common sense once you have seen it.

Keys and referential integrity

A natural key is data with meaning — an ISBN, a country code, an email address. A surrogate key is a generated value with no meaning: an identity column, a sequence, a UUID.

Natural keys read well in queries and remove a join when the child only needs the code (ORDER BY country_code needs no lookup). They fail when the "immutable" identifier turns out to be mutable, when it is long enough to bloat every index that references it, or when an external body redefines it.

Surrogate keys are stable and narrow, which is why they are the common default. Use a database-generated identity column (GENERATED ALWAYS AS IDENTITY in PostgreSQL and Oracle 12c or later) unless you need to create identifiers before insertion or merge data from several systems, in which case a UUID is a good fit — a UUID generator is useful for seeding test rows. The critical rule when you use a surrogate: still declare a unique constraint on the natural key, or the table happily stores the same real-world entity twice.

Whatever you choose, let the database enforce the rules. Foreign keys guarantee that a signup cannot reference a session that does not exist; NOT NULL and CHECK constraints encode facts that application validation misses under concurrency; UNIQUE prevents duplicates that no amount of application code can reliably prevent across two servers. Choose delete behaviour deliberately: ON DELETE CASCADE for rows that cannot exist without the parent, RESTRICT for anything a human should confirm, SET NULL only where the null genuinely means "no longer linked". Index every foreign key column, or deleting a parent scans the child table — the index and execution plan guide explains why.

Denormalising on purpose

Denormalisation means knowingly storing a fact twice to make a read cheaper. It is a legitimate technique with one entry requirement: a mechanism that keeps the copies consistent.

Reasonable cases include storing a computed order_total maintained by a trigger or by the same transaction that writes the lines; copying the historical price onto an order line, which is not really redundancy because the current product price is a different fact; keeping a materialised view or summary table for a dashboard; and the star schemas of analytics, where dimension tables are denormalised on purpose because the data is loaded, not edited — see business intelligence.

Unreasonable cases share one trait: nothing keeps the copy true. A customer's city copied onto every order "for reporting", updated by no one, silently becomes fiction.

Schema smells

  • Entity-attribute-value. A table of (entity_id, attribute_name, value) promises infinite flexibility and delivers untyped data, unconstrainable rules and self-joins for every query. Use it only for genuinely user-defined fields, and prefer a jsonb column with a validating check constraint.
  • The wide table. Eighty columns, most null for most rows, usually means several entities sharing one table, or an inheritance hierarchy that was flattened without thought.
  • Nullable everything. If almost every column allows null, the schema has stopped documenting the domain. Each nullable column should have a stated meaning for its null.
  • Booleans that should be states. is_active, is_cancelled and is_archived on one row can encode impossible combinations. A single status column with a check constraint cannot.
  • Repeating column groups. address_line_1 through address_line_5, or q1_total through q4_total, are rows pretending to be columns.
  • No foreign keys "for performance". The cost of a foreign key is one index probe per write; the cost of orphaned data is a permanent tax on every query and every developer, and it also robs the optimizer of information, as the SQL best practices guide explains.

When an ORM sits on top, the schema still comes first: entity mappings should describe a good relational design rather than dictate it, a point covered in JPA entity mapping.

Key takeaways

  • Model entities and relationships first, then write the functional dependencies; redundancy is a consequence of dependencies, so the dependencies tell you where to split.
  • 1NF removes repeating groups, 2NF removes partial dependencies on a composite key, 3NF removes transitive dependencies, and BCNF requires every determinant to be a candidate key.
  • Redundancy shows up as update, insertion and deletion anomalies — concrete failures, not stylistic complaints.
  • Surrogate keys are the stable default, but always keep a unique constraint on the natural key so duplicates cannot enter.
  • Let constraints enforce the model: foreign keys with deliberate delete rules, NOT NULL, CHECK and UNIQUE.
  • Denormalise only for a named query, after measurement, and only with a mechanism that keeps the duplicated value correct.

Sources and further reading

Frequently asked questions

Is third normal form enough for a real application?
For the vast majority of transactional schemas, yes. 3NF removes the redundancy responsible for almost every update anomaly you will meet in practice, and Boyce-Codd normal form only differs from it in tables with overlapping composite candidate keys, which are rare outside textbooks. Aim for 3NF everywhere, check BCNF where composite keys overlap, and stop there unless a specific anomaly proves otherwise.
Does normalization make queries slower?
It trades wider rows for more joins. Modern optimizers join indexed tables very efficiently, and narrower tables mean more rows per page and less I/O, so normalised schemas often read faster, not slower. Writes are almost always faster because a fact is stored once. Measure before assuming: an execution plan will tell you whether a join or a scan is the real cost.
Should every table have an auto-generated id column?
Not automatically. A surrogate key is the right default for entities whose natural identifier is unstable, long or externally controlled. Pure join tables between two entities usually need no extra column: the pair of foreign keys is already a perfect primary key, and adding an id lets duplicate pairs slip in unless you also declare a unique constraint on them.
When is denormalisation the right answer?
When you can name the query it accelerates, you have measured that the normalised form is too slow, and you have a mechanism that keeps the copy correct. Storing a computed order total, caching a display name or building a reporting table are all reasonable. Copying a mutable attribute into a second table with nothing to keep the two in step is not.