Data Modeling
First normal form helps you remove repeating groups by moving repeated attributes into a separate child table, then carrying the parent key into that child table. The practical goal is not to split tables for its own sake. The goal is to make each repeated thing addressable as a row while preserving the relationship to the original business object.
What first normal form fixes
First normal form is often summarized as requiring atomic values, but that shorthand can be misleading. In practical data modeling, the more useful warning sign is a repeating group: several columns or packed values that represent multiple occurrences of the same concept inside one parent row.
Common examples include:
product_1, product_2, product_3 on an order row.
phone_home, phone_work, phone_mobile when the business really needs many contact methods.
tag_list containing comma-separated tags.
month_01_sales through month_12_sales when each month is a separate observation.
The modeling problem is that the repeated thing cannot be handled uniformly. You cannot easily filter all order items, count all contact methods, enforce one rule for every tag, or add a fourth product without changing the table shape.
A first-normal-form correction usually creates two levels: the parent entity and the repeated child entity. The parent keeps facts that happen once for the parent. The child stores one row per repeated occurrence.
The parent relationship must survive the reshape
The most common mistake is to extract repeated values into a new table but fail to keep the relationship to the original parent. If you move order items into a separate table, each item row must still identify the order it belongs to. Otherwise, you have cleaned the columns but damaged the meaning.
Think of the reshape as a promise:
Every parent row remains identifiable by its key.
Every child row carries the parent key.
The child table has a clear grain, such as one row per order line or one row per customer contact method.
The child table has either its own key or a reliable key made from the parent key plus a child-level discriminator.
The parent-child relationship is what lets analysts reconstruct the business event. Without it, the child rows become orphaned facts.
Do not remove repeated columns until you know the parent key that will travel with every child row. The parent key is what preserves meaning during the reshape.
Hypothetical dataset: repeated order item columns
Consider a small hypothetical order table used by an operations team. Each order may contain up to three products. The early design stores those products as repeated columns on the order row.
This looks convenient when every order is small. It becomes fragile as soon as someone asks a normal operational question: Which products were ordered last week? What was the average quantity per product? Which order lines had missing quantities?
The issue is not that orders and products exist in the same report. The issue is that multiple order items are being stored as separate attributes of one order row, even though each item is an occurrence of the same kind of thing.
| order_id | customer_id | order_date | product_1 | qty_1 | product_2 | qty_2 | product_3 | qty_3 |
|---|---|---|---|---|---|---|---|---|
| 1001 | C-17 | 2026-09-01 | PEN | 2 | NOTEBOOK | 1 | ||
| 1002 | C-44 | 2026-09-01 | MUG | 1 | ||||
| 1003 | C-17 | 2026-09-02 | PEN | 1 | PEN | 3 | BAG | 1 |
Reshape repeated attributes into parent and child tables
The first-normal-form reshape separates the order from the order items.
The parent table keeps one row per order. It contains attributes that describe the order as a whole: order identifier, customer identifier, order date, order status, and other values that occur once for the order.
The child table keeps one row per order item. It contains the parent order identifier plus item-level attributes: line number, product identifier, quantity, unit price, or fulfillment status.
This does not lose the parent relationship because order_id is carried into every order item row. In relational terms, the child table can be joined back to the parent using the parent key.
The corrected model changes the question from “which numbered product column should I read?” to “which child rows belong to this order?” That is the main operational benefit.
| Table | Grain | Example columns |
|---|---|---|
| orders | One row per order | order_id, customer_id, order_date |
| order_items | One row per order line | order_id, line_number, product_id, quantity |
Worked example: from columns to rows
Start with the repeated columns and create child rows from each non-empty repeated slot. Each slot becomes one child row. The slot number can become a line number if it has business meaning or at least a stable ordering meaning in the source.
For example, an order with two populated item slots becomes two order item rows. An order with three populated item slots becomes three order item rows. The order attributes are not copied into every child column as if the child were a standalone order; they remain in the order table and are referenced by key.
A safe reshape rule is:
Identify the parent key, such as order_id.
Identify the repeated group, such as product, quantity, and unit price repeated by slot.
Create one child row per populated repeated slot.
Carry the parent key into the child row.
Add a child discriminator, such as line_number, if multiple child rows can belong to the same parent.
Keep parent-level attributes in the parent table unless they genuinely vary by child row.
The resulting child grain is clear: one row per order line. That statement matters because it tells future practitioners how to interpret, join, deduplicate, and test the table.
| order_id | line_number | product_id | quantity |
|---|---|---|---|
| 1001 | 1 | PEN | 2 |
| 1001 | 2 | NOTEBOOK | 1 |
| 1002 | 1 | MUG | 1 |
| 1003 | 1 | PEN | 1 |
| 1003 | 2 | PEN | 3 |
| 1003 | 3 | BAG | 1 |
Choose a child key that matches the child grain
After moving repeating groups into child rows, the next modeling decision is the child key. A child table needs a way to identify each child row without confusing it with its siblings.
For order lines, common choices include:
Composite key: order_id plus line_number.
Surrogate child key: order_line_id, with order_id still stored as the parent reference.
Source-system child key: a line identifier from the operational source, if the source already provides one.
The key should match the business grain. If the business can have the same product twice on one order as two separate lines, then order_id plus product_id is not a safe key. It would collapse two valid child rows into one. If the business always combines the same product into one line per order, then order_id plus product_id may be acceptable, but that is a business rule, not a normalization rule.
The parent relationship and the child identity are related but not identical. The parent key says where the child belongs. The child key says which child row it is.
The parent key links the child to its parent. The child key identifies one child row among that parent’s children. A model often needs both.
Counterexamples that break the parent relationship
Repeating groups are easy to fix mechanically and easy to fix incorrectly. The following counterexamples show designs that appear cleaner but still damage relational meaning.
Counterexample 1: child rows without the parent key. A table with product_id, quantity, and unit_price but no order_id cannot tell which order each row came from. The repeated columns are gone, but the order relationship is gone too.
Counterexample 2: one generic sequence for all child rows. A global line_number without order_id does not identify an order line unless line numbers are globally unique. In most order systems, line 1 repeats for many orders. The child identity needs either a true child key or a composite such as order_id plus line_number.
Counterexample 3: copying all parent columns into the child table. If every order item row repeats customer_id, order_date, and order_status, the model may still work for some queries, but updates become risky. If the order status changes, the same status must be updated on every child row. The child table is no longer just child data; it has become a duplicated order table.
Counterexample 4: packing the repeated group into JSON or a comma-separated field and calling it done. Sometimes nested data is appropriate in a source system or interchange format. But if the relational model must filter, join, validate, or aggregate each repeated item as its own record, packing the values into one field has not solved the repeating-group problem. It has hidden it.
| Bad pattern | What goes wrong | Safer pattern |
|---|---|---|
| Extract child values without order_id | Child rows become orphaned and cannot be tied to an order | Carry order_id into every order item row |
| Use product_id as the child key | The same product may appear on multiple orders or multiple lines | Use order_id plus line_number, a source line id, or a surrogate order_line_id |
| Copy every order column into each item row | Parent facts can become inconsistent across repeated child rows | Keep order facts in orders and item facts in order_items |
| Keep a comma-separated product list | Products are still not individually addressable as relational rows | Store one product occurrence per child row |
Querying after the reshape
Once the repeated attributes are stored as child rows, ordinary relational joins can reconnect parent and child data for analysis. The join expresses the relationship directly: order rows connect to order item rows through the parent key.
For example, a report can count items by order date by joining orders to order items on order_id, grouping by the order date, and aggregating the child rows. A product analysis can filter the child table by product_id and then join back to orders for customer or date context.
The important modeling point is that the join is no longer a workaround around product_1, product_2, and product_3. It is the intended relationship between two tables with clear grains.
In practice, choose the join type based on the question. If you need all order items with their order context, an inner join may be appropriate. If you need all orders, including orders with no item rows because of source defects or optional detail, a left join from orders to order items may be needed. The normalization decision gives you the structure; the analytical question determines the query.
Handle missing and optional repetitions deliberately
Repeating groups often contain blanks. For example, product_3 may be empty because the order only has two items. That blank should usually produce no third child row. It means there is no third item, not that there is an item with an unknown product.
Distinguish these cases:
No occurrence: there is no child row. An order with two items has two order item rows, not three rows with one null product.
Unknown attribute on a real occurrence: there is a child row, but one child attribute is unknown. For example, an item exists but its unit price has not arrived yet.
Not applicable: the attribute does not apply to that child type. Consider modeling separate child types if many fields apply only to some rows.
This distinction prevents accidental row inflation. It also makes quality checks sharper: a missing quantity on an existing order line is a data quality issue, while a missing third item on a two-item order is normal.
A blank repeated slot is often not a null child attribute. It may mean no child occurrence exists at all.
Practitioner checklist for reshaping repeating groups
Use this checklist when converting repeated attributes into first-normal-form tables.
Name the parent grain. Example: one row per order.
Name the repeated child grain. Example: one row per order line.
Identify the parent key. Example: order_id.
Carry the parent key into the child table. Do not extract child rows without the parent reference.
Choose a child key. Use a source child identifier, a surrogate key, or a composite key such as order_id plus line_number.
Move only child-level attributes into the child table. Keep parent-level attributes in the parent table.
Decide how to treat blanks. Do not create fake child rows for repeated slots that simply do not exist.
Test reconstruction. Pick several original parent rows and verify that the reshaped parent and child rows describe the same business event.
Write the grain in plain English. Future maintainers should not have to infer it from column names.
The final test is simple: can every child row answer “which parent do I belong to?” and can every parent be understood without storing an arbitrary maximum number of child attributes?
Key takeaways
- A repeating group is a sign that several occurrences of the same concept are being stored as columns or packed values inside one parent row.
- A first-normal-form reshape usually creates a parent table and a child table, not just a different column layout.
- The child table must carry the parent key so each child row can be traced back to the parent business object.
- The child table also needs a clear grain and a reliable child identity, such as a source line id or order_id plus line_number.
- Blank repeated slots usually mean no child occurrence exists; they should not automatically become null-valued child rows.
Next step
Take one table with numbered columns such as item_1, item_2, and item_3. Write the parent grain, write the child grain, choose the parent key, then sketch the child table with one row per repeated occurrence. Verify three sample parent rows by reconstructing the original business meaning from the parent and child tables.
- Read Second and Third Normal Forms in an Order Dataset: A practical guide to separating partial and transitive dependencies without turning normalization into theory theater.
- Read Set semantics versus bag semantics in SQL: A practical guide to showing why duplicate rows survive normal SQL queries unless you explicitly remove or prevent them.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.