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
- 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.
- 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.
- 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.
- 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."
1---2name: database-normalization3description: Explains and applies the database normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) and strategic denormalization, with worked examples in PostgreSQL, MySQL, SQLite, MongoDB, Cassandra, ScyllaDB, and DynamoDB. Use when users ask about normal forms, functional/multivalued/join dependencies, anomalies (insertion/update/deletion), schema design and refactoring, when they need help deciding how far to normalize a schema, or when they want a normalization concept translated into a specific database's modeling idioms (relational tables, MongoDB embedding/referencing, Cassandra/ScyllaDB query-driven tables, DynamoDB single-table design).4---56# Database Normalization78Ground 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.910## When to Use1112- The user names a normal form directly ("what is BCNF", "show me 3NF in PostgreSQL")13- 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")14- The user asks whether a schema is normalized, or asks to normalize/denormalize a schema15- 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?")16- The user wants existing tables or documents refactored toward or away from a normal form1718## Workflow19201. **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.212. **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.223. **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.234. **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.2425If 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.2627## Normal Form Catalog2829### Core Forms3031The forms nearly every relational schema should reach; each eliminates a specific class of anomaly caused by a specific kind of dependency.3233| Form | Eliminates | Reference |34| ---- | ------------------------------------------------------------------------- | -------------------------------------------------------------------- |35| 1NF | Repeating groups and non-atomic (multi-valued) column values | [references/first-normal-form.md](references/first-normal-form.md) |36| 2NF | Partial dependency of a non-key attribute on part of a composite key | [references/second-normal-form.md](references/second-normal-form.md) |37| 3NF | Transitive dependency of a non-key attribute on another non-key attribute | [references/third-normal-form.md](references/third-normal-form.md) |3839### Advanced Forms4041Forms 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.4243| Form | Eliminates | Reference |44| ---- | ------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------- |45| BCNF | Every functional dependency not sourced from a candidate key (a 3NF gap when candidate keys overlap) | [references/boyce-codd-normal-form.md](references/boyce-codd-normal-form.md) |46| 4NF | Multivalued dependencies — independent multi-valued facts forced into one table | [references/fourth-normal-form.md](references/fourth-normal-form.md) |47| 5NF | Join dependencies not implied by candidate keys — lossy binary decompositions of an n-ary relationship | [references/fifth-normal-form.md](references/fifth-normal-form.md) |4849### Beyond Normalization5051The deliberate, informed reversal of normalization for a specific performance or access-pattern reason — the default posture in most NoSQL databases.5253| Topic | Trades | Reference |54| --------------- | -------------------------------------------------------------------------------- | -------------------------------------------------------------- |55| Denormalization | Write-time consistency and storage for read-time speed (fewer joins/round trips) | [references/denormalization.md](references/denormalization.md) |5657## Examples5859Each form has a folder `examples/<form>/` with a worked example per database, named `<database>.<ext>`:6061| Database | File | Notes |62| ---------- | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- |63| PostgreSQL | `postgres.sql` | Runnable DDL/DML; uses `psql`-compatible SQL (identity columns, `CHECK`, `jsonb` where relevant) |64| MySQL | `mysql.sql` | Runnable DDL/DML; calls out `AUTO_INCREMENT`, engine (InnoDB), and syntax that diverges from PostgreSQL |65| SQLite | `sqlite.sql` | Runnable DDL/DML; uses `INTEGER PRIMARY KEY AUTOINCREMENT`, `PRAGMA foreign_keys`, and a summary table instead of materialized views where relevant |66| MongoDB | `mongodb.js` | `mongosh` script; shows the document shape and when embedding vs. referencing plays the role a normal form plays in SQL |67| Cassandra | `cassandra.cql` | CQL script; shows the query-driven, denormalized-by-default table design and where atomicity/dependency concerns still apply |68| ScyllaDB | `scylladb.cql` | CQL script (compatible with Cassandra); same query-driven modeling posture as Cassandra with ScyllaDB-specific operational notes in the reference files |69| DynamoDB | `dynamodb.md` | Walkthrough with item JSON and access patterns (no native query language); shows single-table design as the intentional counterpoint to normalizing |7071Normalization 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.7273## Answering Guidelines7475- 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.76- State the anomaly a form prevents (insertion, update, or deletion anomaly) in terms of the user's own schema, not the abstract definition.77- 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.78- 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.79- When comparing forms, read both reference files and contrast the dependency each one targets, not just "which number is higher."
cookieMonsterDev