Data Modeling
Second and third normal forms help you answer a simple modeling question: is this column a fact about the whole key, part of the key, or another non-key value? In an order dataset, second normal form usually separates order header facts from order line facts. Third normal form then separates customer, product, and salesperson facts from the transactions that reference them.
Start with one order line table
Assume the data is already in first normal form: each row represents one order line, values are atomic, and there are no repeating product columns such as product_1, product_2, and product_3.
Here is a hypothetical table called order_line_flat:
- order_id
- line_no
- order_date
- customer_id
- customer_name
- customer_tier
- salesperson_id
- salesperson_region
- product_id
- product_name
- quantity
- unit_price_at_order
The candidate key for this table is order_id, line_no. One order can have many lines, and each line number is unique only within its order.
The important point is that the key is composite. That creates the possibility of partial dependencies, because a non-key column might depend on only order_id rather than the full key order_id, line_no.
Write the dependencies before moving columns
Normalization is easier when you write down the functional dependencies instead of moving columns by instinct.
In this example, the likely dependencies are:
- order_id, line_no determines product_id, quantity, and unit_price_at_order.
- order_id determines order_date, customer_id, and salesperson_id.
- customer_id determines customer_name and customer_tier.
- salesperson_id determines salesperson_region.
- product_id determines product_name.
These are business rules, not database engine features. If your business allows customer names to change over time, or stores historical product descriptions per order, the dependencies may be different. The modeling work is to state those rules clearly enough that the table design follows them.
| Column | Depends on | Dependency type in order_line_flat | Likely home |
|---|---|---|---|
| order_date | order_id | Partial dependency | orders |
| customer_id | order_id | Partial dependency | orders |
| customer_name | customer_id | Transitive dependency | customers |
| customer_tier | customer_id | Transitive dependency | customers |
| salesperson_region | salesperson_id | Transitive dependency | salespeople |
| product_name | product_id | Transitive dependency | products |
| quantity | order_id, line_no | Depends on whole key | order_lines |
| unit_price_at_order | order_id, line_no | Depends on whole key as a historical line fact | order_lines |
Second normal form removes partial dependencies
A table is in second normal form when it is in first normal form and every non-key attribute depends on the whole candidate key, not just part of it.
In order_line_flat, the key is order_id, line_no. The columns order_date, customer_id, and salesperson_id depend on order_id alone. They do not vary by line number. If order 1001 has three lines, the order date is repeated three times.
That repetition is the signal. These columns describe the order header, not the order line.
To move toward second normal form, separate the table into:
- orders: order_id, order_date, customer_id, salesperson_id
- order_lines: order_id, line_no, product_id, quantity, unit_price_at_order
Now orders has a single-column key, order_id. The order-level facts depend on that full key. order_lines still has the composite key order_id, line_no, and the line-level facts depend on the full line key.
Second normal form asks whether a column depends on the whole key. It is mainly a composite-key test.
Third normal form removes transitive dependencies
A table is in third normal form when it is in second normal form and non-key attributes do not depend on other non-key attributes.
After the second normal form split, imagine you left these columns in orders:
- order_id
- order_date
- customer_id
- customer_name
- customer_tier
- salesperson_id
- salesperson_region
The key order_id determines customer_id. But customer_id determines customer_name and customer_tier. That means the customer facts depend on the order only through another non-key attribute. This is a transitive dependency.
The same issue appears with salesperson_region. The order determines salesperson_id, and salesperson_id determines salesperson_region.
In order_lines, product_name would also be transitive if it were stored there. The line determines product_id, and product_id determines product_name.
To move toward third normal form, keep identifiers in the transaction tables and move descriptive facts to the entity tables they describe.
Third normal form asks whether a non-key column is really describing another non-key column.
A practical 3NF decomposition for the example
A clean third normal form version of this example could look like this:
- orders: order_id, order_date, customer_id, salesperson_id
- order_lines: order_id, line_no, product_id, quantity, unit_price_at_order
- customers: customer_id, customer_name, customer_tier
- products: product_id, product_name
- salespeople: salesperson_id, salesperson_region
The transaction tables now record events: an order was placed, and the order had lines. The entity tables record relatively stable descriptions: who the customer is, what the product is called, and which region the salesperson belongs to.
This split reduces update anomalies. If a customer tier changes, you update the customer record rather than every historical order line. If a product name is corrected, you update the product record rather than every line that referenced that product.
The phrase relatively stable matters. Sometimes a descriptive value is intentionally captured as history. unit_price_at_order belongs on the order line because it is the price charged on that line at that time. A current product list price would be a different fact with a different dependency.
| Normal form | Question | Order dataset action |
|---|---|---|
| Second normal form | Does each non-key column depend on the whole composite key? | Move order header facts out of order lines. |
| Third normal form | Does each non-key column avoid depending on another non-key column? | Move customer, product, and salesperson descriptions into their own tables. |
Counterexamples that prevent the wrong split
Normalization fails when the dependency is assumed instead of checked. Here are common counterexamples in order data.
- Customer name on an invoice: If the business must preserve the exact legal name printed on the invoice, that invoice name may be a historical order fact rather than a current customer fact.
- Product name at purchase time: If the product name shown to the buyer must be preserved exactly, storing a product display name on the order line may be intentional.
- Salesperson region over time: If salespeople can move regions, and reporting needs the region at the time of sale, region assignment may need an effective-dated table rather than a single current region column.
- Customer tier used for pricing: If tier affects the price charged, you may need both the current customer tier and the tier applied to the order.
These are not excuses to ignore second and third normal forms. They are reminders that the dependency must match the business meaning of the column.
Before moving a descriptive column out of a transaction table, decide whether it means current description or historical value captured at the time of the event.
Diagnostic questions for practitioners
When you are unsure whether a column violates second or third normal form, ask these questions in order.
- What is the candidate key of this table? Do not start by naming tables. Start by knowing what makes a row unique.
- Does the column depend on the whole key? If the key is composite and the column depends on only one part, you have a partial dependency.
- Does the column depend on another non-key column? If yes, you likely have a transitive dependency.
- Is the column a current descriptive fact or a historical transaction fact? Current descriptions usually belong with the entity. Historical facts may belong with the transaction.
- Would updating this fact require changing many repeated rows? Repeated updates are a practical smell, even before you use formal terminology.
The goal is not to produce the maximum number of tables. The goal is to put each fact where its determinant lives.
Use keys and foreign keys to preserve meaning
After decomposition, constraints make the model enforceable instead of merely documented.
- Primary keys identify each row in tables such as orders, customers, and products.
- Composite primary keys can identify rows in tables such as order_lines, where order_id, line_no uniquely identifies a line.
- Foreign keys connect transaction rows to referenced entities, such as orders.customer_id referencing customers.customer_id.
Queries can then join the normalized tables when a report needs order, customer, product, and salesperson context together. The model stores each fact once, while queries assemble the view needed for analysis.
Common failure modes
Watch for these mistakes when applying second and third normal forms to order data.
- Treating every repeated value as a problem: Repetition is a clue, not proof. First identify the dependency.
- Moving historical facts into current entity tables: This can rewrite history by accident. Prices, names, tiers, and regions may need time-aware treatment.
- Ignoring composite keys: Second normal form is most visible when the table has a composite key. If you do not name the full key, partial dependencies stay hidden.
- Over-normalizing labels used only for convenience: Not every small code needs its own table if it has no independent lifecycle or attributes.
- Confusing modeling with performance tuning: Normal forms describe semantics. Indexing, materialization, and query performance are separate design decisions.
Key takeaways
- Second normal form removes partial dependencies: facts that depend on only part of a composite key.
- Third normal form removes transitive dependencies: facts that depend on another non-key fact.
- In an order dataset, order header fields usually separate from order line fields during 2NF.
- Customer, product, and salesperson descriptions usually separate from transaction tables during 3NF.
- Historical transaction facts, such as price charged at order time, may correctly remain on the order line.
Next step
Take one real order or invoice table and write its candidate key at the top. For each non-key column, write what determines it. Mark columns that depend on part of the key as 2NF issues, and columns that depend on another non-key column as 3NF issues.
- Read Lossless Decomposition of Relational Tables: A practical guide to proving whether decomposed tables join back to the original records without missing or invented rows.
- Read First Normal Form and Repeating Groups: How to reshape repeated attributes into child rows while keeping the original parent record clear and queryable.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.