Database normalization

Database Normalization

Ground normalization answers in the reference files bundled with this skill instead of improvising definitions. Each normal form has a reference file explaining it and a folder of worked examples across popular databases.

When to Use

• The user names a normal form directly ("what is BCNF", "show me 3NF in PostgreSQL")
• The user describes an anomaly that maps to a missing normal form ("if I delete the last order for a customer I lose their address")
• The user asks whether a schema is normalized, or asks to normalize/denormalize a schema
• The user asks how a normalization concept applies (or doesn't) in a non-relational database ("does 1NF matter in MongoDB?", "how do I avoid joins in DynamoDB?")
• The user wants existing tables or documents refactored toward or away from a normal form

Workflow

1. Identify the normal form(s) from the user's question using the catalog below. If the user describes symptoms (repeating groups, partial/transitive dependency, update anomalies) rather than naming a form, diagnose which form addresses it.
2. Read the reference file at references/<form>.md for the definition, the dependency it eliminates, the anomalies it prevents, and when going further stops paying off.
3. Read the database example at examples/<form>/<database>.<ext> when the user works in (or asks for) a specific database. Adapt it to their schema rather than pasting the generic example verbatim.
4. Answer with: what the form requires, what anomaly it prevents, and a schema/model adapted to the user's context — including whether that database's idioms call for staying normalized or deliberately denormalizing.

If the user's schema is already anomaly-free at a given form, say so — do not push further normalization for its own sake. Every step past 3NF/BCNF is a judgment call against real anomalies, not a checklist to exhaust.

Normal Form Catalog

Core Forms

The forms nearly every relational schema should reach; each eliminates a specific class of anomaly caused by a specific kind of dependency.

Form	Eliminates	Reference
1NF	Repeating groups and non-atomic (multi-valued) column values	references/first-normal-form.md
2NF	Partial dependency of a non-key attribute on part of a composite key	references/second-normal-form.md
3NF	Transitive dependency of a non-key attribute on another non-key attribute	references/third-normal-form.md

Advanced Forms

Forms that close gaps 3NF leaves open — situations 3NF permits but that still cause anomalies. Most production schemas stop at 3NF/BCNF; 4NF and 5NF apply to specific dependency shapes.

Form	Eliminates	Reference
BCNF	Every functional dependency not sourced from a candidate key (a 3NF gap when candidate keys overlap)	references/boyce-codd-normal-form.md
4NF	Multivalued dependencies — independent multi-valued facts forced into one table	references/fourth-normal-form.md
5NF	Join dependencies not implied by candidate keys — lossy binary decompositions of an n-ary relationship	references/fifth-normal-form.md

Beyond Normalization

The deliberate, informed reversal of normalization for a specific performance or access-pattern reason — the default posture in most NoSQL databases.

Topic	Trades	Reference
Denormalization	Write-time consistency and storage for read-time speed (fewer joins/round trips)	references/denormalization.md

Examples

Each form has a folder examples/<form>/ with a worked example per database, named <database>.<ext>:

Database	File	Notes
PostgreSQL	postgres.sql	Runnable DDL/DML; uses psql-compatible SQL (identity columns, CHECK, jsonb where relevant)
MySQL	mysql.sql	Runnable DDL/DML; calls out AUTO_INCREMENT, engine (InnoDB), and syntax that diverges from PostgreSQL
SQLite	sqlite.sql	Runnable DDL/DML; uses INTEGER PRIMARY KEY AUTOINCREMENT, PRAGMA foreign_keys, and a summary table instead of materialized views where relevant
MongoDB	mongodb.js	mongosh script; shows the document shape and when embedding vs. referencing plays the role a normal form plays in SQL
Cassandra	cassandra.cql	CQL script; shows the query-driven, denormalized-by-default table design and where atomicity/dependency concerns still apply
ScyllaDB	scylladb.cql	CQL script (compatible with Cassandra); same query-driven modeling posture as Cassandra with ScyllaDB-specific operational notes in the reference files
DynamoDB	dynamodb.md	Walkthrough with item JSON and access patterns (no native query language); shows single-table design as the intentional counterpoint to normalizing

Normalization is a relational concept built on functional/multivalued/join dependencies. The PostgreSQL, MySQL, and SQLite examples are direct applications of a form. The MongoDB, Cassandra, ScyllaDB, and DynamoDB examples show the analogous problem (an anomaly-causing shape) and that database's idiomatic answer, which is frequently "duplicate the data on purpose" rather than "add another table." The reference files explain why that's a rational trade-off there, not a violation of best practice.

Answering Guidelines

• Name the dependency (functional, partial, transitive, multivalued, join) causing the anomaly — "normalize this" without naming the dependency doesn't help the user recognize the next case themselves.
• State the anomaly a form prevents (insertion, update, or deletion anomaly) in terms of the user's own schema, not the abstract definition.
• Don't recommend normalizing past the point where it prevents a real anomaly in the user's data; 4NF and 5NF are narrow tools for narrow dependency shapes, not a ladder to climb by default.
• For MongoDB, Cassandra, ScyllaDB, and DynamoDB, lead with access patterns and consistency requirements, not with "how do I make this look like a normalized relational schema" — read the matching reference's database-specific notes before answering.
• When comparing forms, read both reference files and contrast the dependency each one targets, not just "which number is higher."