Data Normalization
Guides the process of normalizing database schemas through the normal forms (1NF, 2NF, 3NF, BCNF, 4NF) to eliminate data redundancy, prevent update anomalies, and ensure data integrity. Covers functional dependency identification, table decomposition techniques, lossless join verification, dependency preservation, and strategic denormalization decisions for read performance optimization.
Usage
Provide your current table schema or describe the data you need to model. Specify the current issues (duplicate data, update anomalies, inconsistencies) or ask for a normalization analysis of an existing schema. The skill walks through each normal form, identifies violations, and produces a properly normalized schema with explanations.
Examples
- "Normalize this orders table that stores customer name, address, product name, and category in every row"
- "Analyze this schema for 3NF violations: users table with city, state, zip_code, and country columns"
- "Decompose this flat CSV import table into a properly normalized relational schema with foreign keys"
- "My 3NF schema is causing too many JOINs for the dashboard query — advise on strategic denormalization"
Guidelines
- 1NF: Eliminate repeating groups and multi-valued columns — each cell should contain one atomic value
- 2NF: Remove partial dependencies — non-key columns must depend on the entire composite primary key
- 3NF: Remove transitive dependencies — non-key columns must depend only on the primary key, not other non-key columns
- BCNF: Every determinant must be a candidate key — handles edge cases that 3NF misses with overlapping keys
- Identify functional dependencies by asking: does knowing X uniquely determine Y? (X → Y)
- Verify decompositions are lossless: joining the decomposed tables must reproduce the original data exactly
- Denormalize strategically only after measuring actual query performance problems, not preemptively
- Common acceptable denormalizations: cached aggregates (order_total), copied fields for display (customer_name on order)