Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Schema design and SQL practice
Page 7 of 20

Schema review: turn requirements into constraints

Review a concrete multi-tenant order schema for grain, identity, dependencies, integrity boundaries, and migration-safe choices.

Lesson briefSchema design and SQL practice · 8 h · 3 outcomes

Required knowledge

  • Relational-model grain, keys, foreign keys, and NULL semantics
  • Functional dependencies and normalization trade-offs

Learning outcomes

  • Review a relation by naming its grain, candidate key, foreign keys, and facts that must not be duplicated.
  • Detect a cross-tenant reference, mixed historical/current fact, or unsupported constraint in a proposed schema.
  • Write a focused schema-review note with test cases and migration questions.

Assessment target

Given a proposed commerce schema and a business change, identify integrity risks, propose focused constraints or decompositions, and supply adversarial rows that test the design.

Scope boundary

  • Online migration tooling, query-plan tuning, and distributed consistency are treated in later courses.

A schema review is a testable argument, not a naming review. For every relation, ask: what is one row? What identifies it? Which other row must exist? Which value is a historical fact rather than a lookup? Then write the smallest constraint or test that would catch a violation.

Review one multi-tenant order design

An online marketplace gives every seller a tenant. Product and order identifiers are tenant-scoped, so a foreign key must carry the tenant identity too.

Code
class Tenant {
tenant_id : UUID <<PK>>
}
class Product {
tenant_id : UUID <<PK>>
product_id : UUID <<PK>>
sku : text
list_price : money
}
class PurchaseOrder {
tenant_id : UUID <<PK>>
order_id : UUID <<PK>>
customer_id : UUID
placed_at : timestamp
}
class OrderLine {
tenant_id : UUID <<PK>>
order_id : UUID <<PK>>
line_no : integer <<PK>>
product_id : UUID <<FK>>
quantity : integer
unit_price : money
}
Tenant "1" -- "0..*" Product
Tenant "1" -- "0..*" PurchaseOrder
PurchaseOrder "1" -- "1..*" OrderLine
Product "1" -- "0..*" OrderLine : selected product
Tenanttenant_id : UUID «PK»Producttenant_id : UUID «PK»product_id : UUID «PK»sku : textlist_price : moneyPurchaseOrdertenant_id : UUID «PK»order_id : UUID «PK»customer_id : UUIDplaced_at : timestampOrderLinetenant_id : UUID «PK»order_id : UUID «PK»line_no : integer «PK»product_id : UUID «FK»quantity : integerunit_price : money10..*10..*11..*selected product10..*
RelationGrainKeyCore reference or rule
Product(tenant_id, product_id, sku, list_price)One catalog product for one tenant(tenant_id, product_id)sku may need UNIQUE(tenant_id, sku) if the business calls it a tenant-local identifier
PurchaseOrder(tenant_id, order_id, customer_id, placed_at)One placed order for one tenant(tenant_id, order_id)Tenant-scoped customer reference, if customers are tenant-scoped
OrderLine(tenant_id, order_id, line_no, product_id, quantity, unit_price)One product line in one order(tenant_id, order_id, line_no)Composite reference to its order and selected product; quantity > 0

Worked example. unit_price belongs on OrderLine when it records the price actually agreed at purchase time. Product.list_price is a current catalog fact and may change tomorrow. Replacing unit_price with a lookup from Product changes historical orders retroactively; it is not a harmless normalization.

Predict the tenant leak

Tenant A has product_id = p9; tenant B also has product_id = p9. An order line for tenant A references only product_id = p9. What incorrect fact can the database accept if the foreign key is only OrderLine.product_id → Product.product_id?

HintExpand

The identifier is scoped by tenant, so the product ID alone is ambiguous.

Worked solutionExpand

The order line can be associated with tenant B’s product even though the order belongs to tenant A. The database sees a matching product ID but cannot prove tenant consistency. Make (tenant_id, product_id) a referenced key of Product and reference both columns from OrderLine.

Code Exercise

2 tokens to supply

Keep the tenant in the product reference

FOREIGN KEY (tenant_id, product_id)
REFERENCES Product (, )

Walk the review questions in order

Review questionEvidence to requestA failure it catches
What is one row?A grain sentence for each relationA line item table that also stores order-wide shipping state
What identifies it?Candidate keys and whether they are stableA display label silently used as a primary key
What must exist first?Foreign keys and deletion/update policyAn order line with no order or wrong-tenant product
Which values are derived or historical?Source of truth and update ruleRepricing past orders by joining current catalog price
Which rules span rows?Transaction/procedure/test designTwo writes that independently pass an aggregate capacity rule
How will it change?Backfill, compatibility, rollback, and verification planA non-null column added before legacy rows are populated

Review a mixed-grain proposal

A teammate proposes this shortcut:

OrderLine(tenant_id, order_id, line_no, product_id, quantity,
          order_status, placed_at, customer_email)

Name two facts with the wrong grain. Where should each live instead?

HintExpand

A line is one product occurrence; ask whether each value is the same for every line of an order.

Worked solutionExpand

order_status and placed_at are facts about the whole order, so they belong in PurchaseOrder. customer_email is normally a customer fact, so it belongs in a tenant-scoped customer relation or an explicitly justified immutable order-contact snapshot. Repeating order-level values on every line causes update anomalies and makes a changed line capable of contradicting its own order.

Turn dependencies into constraints and tests

For this design, the following dependencies express useful review hypotheses:

(tenant_id,product_id)→(sku,list_price)(tenant\_id, product\_id) \to (sku, list\_price) (tenant_id,order_id,line_no)→(product_id,quantity,unit_price).(tenant\_id, order\_id, line\_no) \to (product\_id, quantity, unit\_price).

They justify the relation keys only if the product and line identifiers really are stable in the business domain. A natural key such as SKU may also be unique per tenant, but only after spelling out capitalization, reuse, and retirement policy.

Counterexample. Adding UNIQUE(sku) globally because the current export has no duplicates can reject two independent sellers’ legitimate use of the same SKU. The requirement is usually tenant-local uniqueness, which has a different key and different error behavior.

Make an adversarial test set

Write four insert attempts that should not all succeed:

  1. a duplicate line number in one order;
  2. a zero or negative quantity;
  3. a line that names a missing order;
  4. a line for tenant A that names tenant B’s product ID.
HintExpand

Map each attempt to a distinct constraint; the last one needs the composite key.

Worked solutionExpand

The duplicate is rejected by PRIMARY KEY (tenant_id, order_id, line_no). The invalid quantity is rejected by CHECK (quantity > 0). The missing order is rejected by FOREIGN KEY (tenant_id, order_id) REFERENCES PurchaseOrder. The cross-tenant product is rejected only when the product foreign key is (tenant_id, product_id) REFERENCES Product(tenant_id, product_id). These tests establish different invariants; one successful happy-path order proves none of them.

Review a change before the migration is written

The product team wants to allow a line to have no catalog product after a product is retired, while preserving the original line description and price.

Worked example. This is not just “make product_id nullable.” Decide first whether a line must retain a historical product snapshot, whether product deletion is forbidden, or whether retirement is represented by a status rather than deletion. If the relationship becomes optional, update the foreign-key nullability, the reporting contract, and tests for null product references together.

Section Exercises

Deep Practice

Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.

Exercise 2.3.1: Composite Key Multi-Tenant Isolation

Verificationapplication

In a multi-tenant order schema, an engineer declares OrderLine(tenant_id, order_id, line_no, product_id, quantity) with foreign key FOREIGN KEY (product_id) REFERENCES Product(product_id). Verify why this foreign key fails to prevent cross-tenant data corruption, and specify the correct multi-column constraint.

HintExpand

The foreign key must reference both the tenant identifier and the product identifier.

Worked solutionExpand

Because product_id is only unique within a tenant, referencing Product(product_id) alone permits an order in tenant AA to reference a catalog product belonging to tenant BB The constraint must enforce multi-tenant scoping: FOREIGN KEY (tenant_id, product_id) REFERENCES Product(tenant_id, product_id), backed by a matching composite candidate key on Product.

Exercise 2.3.2: Order Line-Item Grain and Constraint Definition

Constructionapplication

Specify SQL table definitions for PurchaseOrder and OrderLine with composite tenant keys. Include primary keys, referential integrity foreign keys, and a check constraint ensuring line quantities are strictly positive.

HintExpand

Include tenant_id in primary and foreign keys of both relations.

Worked solutionExpand
CREATE TABLE PurchaseOrder (
  tenant_id UUID NOT NULL,
  order_id BIGINT NOT NULL,
  ordered_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);

CREATE TABLE OrderLine (
  tenant_id UUID NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (tenant_id, order_id, line_no),
  FOREIGN KEY (tenant_id, order_id) REFERENCES PurchaseOrder(tenant_id, order_id)
);

Exercise 2.3.3: Product Retirement Historical Snapshot Modeling

Descriptionrecall

When a catalog product is retired, existing order lines must preserve the purchase price and product description at the time of sale. Explain why making product_id nullable without capturing historical price attributes violates relational schema design principles.

HintExpand

Distinguish between the current catalog state and an immutable transaction line snapshot.

Worked solutionExpand

An order line records an immutable historical financial event. If product_id is set to NULL or points to an updated/deleted product row, historical reports either lose identity or recompute totals using altered prices. The schema must store historical unit price and description directly on OrderLine, or retain soft-deleted product records with active status indicators.

Exercise 2.3.4: Discount Code vs. Applied Discount Schema Review

Generalisationtransfer

Review a proposed OrderDiscount(tenant_id, order_id, code, amount) relation. Determine whether its grain represents a discount definition, an applied order discount, or both. State candidate keys, foreign keys, and identify two business rules that must be clarified before finalizing the schema.

HintExpand

Separate coupon definition rules from the event of applying a coupon to a purchase order.

Worked solutionExpand

The grain represents one applied discount on a purchase order. The candidate key is (tenant_id, order_id, code) if each code can apply at most once per order, with foreign key (tenant_id, order_id) REFERENCES PurchaseOrder(tenant_id, order_id). The general definition of codes belongs in a separate Discount(tenant_id, code, percentage, valid_until) relation. Before finalizing, clarify: (1) whether multiple instances of the same discount code can be applied to an order, and (2) whether amount is an immutable financial snapshot or recomputed dynamically upon return or line adjustment.

References & Further Reading