191: Data Modeling, Functional Dependencies, and Normalization

Learning outcomes

By the end of this lesson, you can:

explain and apply functional dependencies in a realistic implementation;
explain and apply first normal form in a realistic implementation;
explain and apply second normal form in a realistic implementation;
explain and apply third normal form in a realistic implementation;
explain and apply bcnf in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you start, retrieve one concrete example from an earlier project where two pieces of data were duplicated, a table was difficult to update safely, or a query had to work around an awkward schema. That example gives the terminology somewhere to land.

The aim is not to memorize a sequence of abbreviations. You should be able to make and defend a schema decision in a PostgreSQL-backed transactional application, where correctness, query plans, and concurrent writes all matter. Normalization helps with those decisions, but it does not replace requirements analysis or measurement.

Terminology

Functional dependencies: A dependency X -> Y says that, within a relation, a given value of X determines one value of Y. If two rows agree on X, the dependency says they must agree on Y.
First normal form: Practical 1NF modeling uses atomic values for the relational design being chosen and avoids repeating column groups. It does not mean that every PostgreSQL array or JSON value is automatically wrong; the question is whether that value is intentionally one domain attribute or an unexamined set of relational facts.
Second normal form: 2NF removes non-key attributes that depend on only part of a composite candidate key. With a single-column key, there cannot be a partial dependency on part of that key.
Third normal form: 3NF removes transitive dependencies in which non-key facts depend on other non-key facts rather than directly on the key. This reduces the number of places where the same fact can be updated inconsistently.
BCNF: Boyce-Codd normal form strengthens the determinant rule used by 3NF. It can reveal anomalies that 3NF permits, although a decomposition must still be evaluated for dependency preservation and practical query cost.
Denormalization: Denormalization duplicates derived or reference data for a measured read, performance, or availability requirement. It is a deliberate trade-off, not a shortcut taken before the source of truth and synchronization strategy are known.

Mental model

Treat Data Modeling, Functional Dependencies, and Normalization as a design problem with observable inputs, outputs, invariants, and failure modes. Begin with the facts the business needs to store and the rules that relate those facts. Functional dependencies make those rules explicit; candidate keys identify the smallest set of attributes that can identify a row; normal forms provide ways to test whether the proposed relation stores those facts without avoidable update anomalies.

Normalization is therefore a reasoning tool, not a command to split every table as far as possible. A strong implementation makes assumptions visible, narrows uncertainty at system boundaries, and leaves evidence such as tests, types, constraints, metrics, or diagrams showing why the design is safe. If a read model is intentionally denormalized, that decision should be just as explicit.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a familiar table template. First state what must remain true. Then identify the keys and dependencies that express those truths, choose a decomposition or representation, and select the database constraints and queries that enforce it.

Deep dive

1. Functional dependencies

Suppose an order line is identified by an order and a product. If each product has one current description, then product_id -> product_description is a functional dependency. If the description is copied into every order-line row, changing the product description requires updating multiple rows, and a missed row creates contradictory data. The dependency exposes why the duplication is risky.

A dependency X -> Y means a value of X determines one value of Y within the relation. Candidate keys are minimal determinants of all attributes: they identify a row, and removing any attribute from the key would stop it from doing so. The word “within” matters. A dependency is a business and data rule for the relation, not a universal property of the column names. If a product can have historical descriptions, for example, the dependency and the model need to include the relevant version or effective date.

Decision rule: Use functional dependencies deliberately when they make the contract or invariant easier to prove. If a dependency only reduces typing while hiding an assumption, prefer the more explicit design. In PostgreSQL, express the rule with appropriate keys, unique constraints, foreign keys, and transaction behavior rather than relying only on application code.

2. First normal form

A table becomes difficult to query when one column quietly contains several values or when the schema creates repeating groups such as phone_1, phone_2, and phone_3. Practical 1NF modeling uses atomic values for the chosen relational design and avoids those repeating column groups. A separate child relation can represent a variable number of phones, order items, or addresses while allowing each value to be constrained and queried independently.

The word “atomic” depends on the design boundary. PostgreSQL arrays and JSON can still be valid when the whole value is intentionally treated as one domain attribute, such as an opaque payload or a document owned by another system. They become a problem when the application needs to filter, uniquely constrain, join, or update the individual elements as relational facts but has stored them as an unexamined blob.

Decision rule: Use first normal form deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Decide whether the value is one domain object or a collection of independently meaningful facts before choosing a column type.

3. Second normal form

2NF matters primarily where composite candidate keys exist. Consider a relation keyed by (order_id, product_id). An order_quantity depends on both parts of that key, but a product_name depends only on product_id. Keeping the product name in every order-line row creates a partial dependency and duplicates product data. Move the product fact to a product relation, leaving the line-specific fact with the composite key.

2NF removes non-key attributes that depend on only part of a composite candidate key. It is not a general instruction to remove every attribute from a table, and it does not apply in the same way to a relation whose only candidate key is one column. You still need to identify all relevant candidate keys rather than assuming that a surrogate primary key tells the whole story.

Decision rule: Use second normal form deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Check both the declared key and the business keys represented by unique constraints before deciding that partial dependencies are absent.

4. Third normal form

A table can satisfy 2NF and still repeat a non-key fact. For example, if customer_id -> customer_region and an order row stores both the customer and the region, the region is a fact about the customer rather than a fact about the order. That is a transitive dependency: the key determines the customer, and the customer determines the region. If the customer changes region, every copied order row becomes a potential stale value.

3NF removes transitive dependencies where non-key facts depend on other non-key facts rather than the key, reducing inconsistent duplicate updates. The practical result is usually a relation for the entity that owns the fact and a foreign key from the relation that refers to it. Historical snapshots are a legitimate exception when the requirement is to preserve what was true at the time of the order; in that case, the snapshot is a deliberate historical fact, not an accidental duplicate.

Decision rule: Use third normal form deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Before moving a column, ask whether it describes the current referenced entity, or whether the requirement explicitly calls for an immutable value captured at an event.

5. BCNF

BCNF strengthens the determinant rule beyond the cases 3NF permits. Informally, every determinant in a BCNF relation must be a candidate key. This can expose a dependency whose determinant is not a key even when the relation passes a 3NF test. Decomposing that relation can prevent anomalies, but it may also make a dependency harder to enforce or require additional joins.

Boyce-Codd normal form strengthens determinant rules and can expose anomalies that 3NF permits. A decomposition is not automatically better simply because it has more tables: also consider whether the stated dependencies remain enforceable, whether the decomposition preserves required dependencies, and what the resulting queries cost. A design that is theoretically cleaner but cannot enforce a critical rule or causes an unjustified performance problem needs further analysis.

Decision rule: Use bcnf deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. State which dependency motivates the decomposition, then verify the result with constraints, representative queries, and the expected transaction workload.

6. Denormalization

Sometimes a normalized schema is not the best read shape for a measured workload. A reporting query may repeatedly derive an order total, or an availability-sensitive read path may need a precomputed projection. Duplicate derived or reference data only for a measured read, performance, or availability requirement. “It might be faster” is not enough evidence by itself.

Before denormalizing, define the source of truth, the writer that updates the duplicate, the transaction or event boundary that synchronizes it, and the strategy for rebuilding it after missed work or a schema change. Decide what stale data means to callers. Indexes, query changes, caching, or a separate reporting model may solve the measured problem without putting duplicate mutable facts into the transactional table.

Decision rule: Use denormalization deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Record the metric that justified the trade-off and the check that will reveal when the trade-off is no longer worthwhile.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence. Then list the input and output contracts, identify the business keys and dependencies, and assign each failure mode to the concept that explains it. This keeps normalization connected to implementation rather than turning it into a naming exercise.

The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. A database constraint can protect an invariant under concurrent requests in a way that a client-side check cannot. Mixing these concerns can make a happy-path demo look shorter, but it leaves edge cases much harder to reason about.

For example, this query returns every active customer, including an active customer with no orders, and counts only the orders matched by the left join:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

There are several details worth preserving when you inspect it. LEFT JOIN retains customers with no matching order, while COUNT(o.id) produces zero for those customers because the joined order id is null. The filter applies to the customer relation, and the grouped columns match the non-aggregated values selected. The query answers a current-state question; it does not by itself establish a historical snapshot or prevent a concurrent order from arriving after the relevant read.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. Also inspect the execution plan when the data volume makes performance relevant. This is the level of explanation expected in a senior code review or technical interview: name the invariant, locate the owner, and describe how you would verify the claim.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. A normalized model can still fail if migrations are unsafe, transactions have incorrect boundaries, or a query plan scans far more rows than expected. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations, including what a retry is allowed to repeat. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and every network input is untrusted; database normalization does not replace authorization, input validation, or careful handling of secrets.

Guided lab

Take an intentionally duplicated order spreadsheet schema. Identify its functional dependencies and update, insertion, and deletion anomalies. Normalize it to a practical 3NF/BCNF design, then justify one deliberately denormalized reporting field. Your justification should name the source of truth, the measured reason for the duplicate, and how the value is synchronized or rebuilt.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing. For database work, this can include constraints, representative rows, logs, or an execution plan.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Functional dependencies: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a case where a presumed dependency is false so you can see which assumption the schema was relying on.
First normal form: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Check whether values stored in arrays or JSON must actually be searched or constrained element by element.
Second normal form: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Use a composite-key example and verify that attributes belonging to only one key component are not repeated in line rows.
Third normal form: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Distinguish a current referenced fact from an intentional historical snapshot.
BCNF: test absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify both the anomaly the decomposition removes and the dependencies or joins it may make more difficult.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Treating every array or JSON value as a normalization violation without asking whether the whole value is one intentional domain attribute.
Assuming a surrogate primary key eliminates business-key dependencies or partial and transitive dependencies.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual rows and constraints or the actual execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. If duplicate facts disagree, identify which relation is supposed to be authoritative before changing data. If the problem appears only under concurrency, inspect transaction boundaries and database constraints instead of assuming that a second application-level check will close the race.

Interview questions

What problem do Functional dependencies solve, and what trade-off or failure mode would make you choose a different approach?
What problem does First normal form solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Second normal form solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Third normal form solve, and what trade-off or failure mode would make you choose a different approach?
What problem does BCNF solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Data Modeling, Functional Dependencies, and Normalization to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be ready to explain which facts are authoritative, which dependencies you used, and which database constraints or tests would catch a violation.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.