Decide which row owns a fact

The shop stores orders and products in separate tables, connected by product ID. The join introduction used that reference to display an order with a product name. Now ask where the name itself should be authoritative.

Suppose every order also stores a field called current_product_name. Product 27 appears in a thousand orders. The seller renames it from “Tote” to “Canvas tote.” If we update only some order rows, different screens disagree about the current name.

Distinguish two meanings before choosing a layout

A value can describe the current product or the purchase as it happened. Those are different facts. A paid price of 20 remains correct for yesterday’s purchase even if today’s price is 25.

Product 27
Current name: Canvas tote
Current price: 25

Order 501
Product reference: 27
Paid price: 20

The product owns its current description. The order owns what was charged for that purchase.

If we want today’s name on the order screen, join to the product row. If we need the exact description printed on the original receipt, store a separately named historical description. Repeated text is not automatically an error; duplicated authority without a maintenance rule is the problem.

Give the dependency a name

Within our product table, a particular product ID determines one current name. This is a functional dependency: once that ID is fixed, the name is fixed in a valid table state. It describes a rule about the data, not a programming function.

Normalization uses such dependencies to organize facts and avoid inconsistent updates. Here, storing the current name once means a rename changes one authoritative row. The orders keep references to the product rather than independent copies of the same current fact.

An engine’s constraints help enforce the model. A unique product ID prevents two authoritative product rows with the same identity. A foreign key prevents a non-missing order reference from pointing to a nonexistent product. Those constraints do not decide what “paid price” should mean; the designer must establish that meaning first.

Choose a deliberate copy

Suppose the order screen must remain readable after a product is removed from the active catalog. Should we necessarily delete the order’s product description too?

Start with the historical requirement

No. A receipt description can be an independent historical fact and remain on the order. Name it accordingly and decide how it is captured when the purchase commits. Do not call it a current product name or update it automatically on every later rename.

The alternative is to retain historical product records. Either approach needs an explicit meaning and lifetime rule.

We can now distinguish logical design, which decides what facts mean, from physical design, which decides where their bytes live. The query front end will use the schema to interpret names and types before it tries to execute a request.

Harvard’s designing lecture develops schema design and relationships. Formal normal forms and more complicated dependencies are in the extended chapter.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter