Schema Design

Introduction

A shop needs to remember customers, products, and purchases. Before creating tables, the designer must decide what each record represents, how records connect, and which rules prevent invalid data.

Schema design defines this structure. In a relational database, the schema specifies tables, columns, data types, keys, relationships, and constraints. A useful schema supports the application’s operations while keeping the meaning of its records clear.

Start with Requirements and Row Meaning

Schema design begins with the information the system must preserve and the questions it must answer. Identifying subjects, organizing attributes, choosing keys, and refining relationships are connected design activities.

For the shop, initial requirements might include:

Customer identification: Every purchase belongs to a recorded customer.
Multiple products: One order can contain several product lines.
Purchase history: Past orders retain the prices agreed when they were placed.
Required reports: Staff can retrieve an order, list its items, and calculate its merchandise total.

Define One Row Before Choosing Its Key

The grain of a table describes what one row represents. Defining it prevents a table from mixing different kinds of facts.

The following diagram separates four subjects in the shop’s schema.

One Row

An order and an order line have different grains. The order records the purchase event; an order line records one item entry within that purchase.

For example, O10 identifies an order. The combination O10 and line 1 identifies a particular line within it. Quantity 2 means that line contains two units, not that it represents two separate orders.

Record Assumptions Explicitly

The example assumes every order has a customer and quantities are positive whole numbers. Guest checkout, fractional quantities, or returns would require additional design decisions.

Requirements determine the schema. Existing sample rows alone cannot establish these rules.

Choose Columns Keys and Relationships

Each column should describe the subject represented by its row. Use meaningful names and choose types that match the values.

For example, Quantity can be an integer when products are sold only in whole units. A price requiring exact decimal arithmetic can use an appropriate decimal type. Dates and timestamps should use temporal types rather than loosely formatted text.

Establish Identity and References

A candidate key is a minimal set of attributes that uniquely identifies a row. Select one as the primary key and preserve other required uniqueness rules.

A foreign key enforces a reference to an eligible key in another table, or sometimes the same table.

The diagram below shows the shop’s proposed structure. Arrows indicate references between tables; PK and FK identify the key columns.

Connect Tables Through Keys

In this design:

CustomerID identifies a customer: Customer names remain descriptive values because different customers may share them.
OrderID identifies an order: CustomerID in ORDER connects the purchase to its customer.
OrderID and LineNo identify an order line: Line numbers can repeat across different orders.
ProductID connects a line to its product: The same product can appear in many order lines.

Using OrderID and LineNo permits the same product to appear on separate lines of one order. If the business prohibits that, add a uniqueness rule for OrderID and ProductID.

Place Facts According to Their Dependencies

A functional dependency describes when one set of attribute values determines another. Dependencies help identify where facts belong and where repetition can cause inconsistent updates.

For example, under the shop’s rules:

Determining identifier	Facts it determines
CustomerID	Current customer name
ProductID	Current product name and catalog price
OrderID	Customer and order timestamp
OrderID and LineNo	Product, quantity, and agreed unit price

Normalization examines such dependencies to reduce problematic redundancy and update anomalies. It complements the broader entity and relationship model.

Separate Repeated Descriptions

Store current customer details in CUSTOMER and current product details in PRODUCT. Orders and order lines can reference them using IDs, so a name or description needs updating in only one place. Keep historical snapshots when the application needs to remember past details.

When splitting tables, consider two things:

Lossless decomposition: Joining the tables should recover the original information without losing data or creating incorrect matches.
Dependency preservation: The original rules between columns should remain enforceable within the separate tables, without joining them first.

Distinguish Current Facts from Historical Facts

CurrentPrice and UnitPriceAtSale describe different facts.

Suppose a notebook currently costs 60 but was sold yesterday for 50. Updating the catalog price must not rewrite yesterday’s agreed price.

Therefore:

CurrentPrice belongs to PRODUCT: It describes the current catalog value.
UnitPriceAtSale belongs to ORDER_LINE: It describes the price agreed for that particular sale.

Historical snapshots are not automatically unnecessary duplication. Their purpose depends on what the system must remember.

For the simplified model, a line amount can be calculated as Quantity multiplied by UnitPriceAtSale. Taxes, discounts, and rounding policies require additional explicit rules.

Turn Business Rules into Constraints

Column names document intent, but database constraints enforce permitted values and relationships.

The following examples show valid and invalid records under the shop’s rules.

Turn Rules Into Constraints

Use Constraints for Distinct Purposes

PRIMARY KEY: Establishes unique, non-null row identity.
FOREIGN KEY: Requires a reference to an existing eligible key.
NOT NULL: Requires a value.
UNIQUE: Prevents prohibited duplicate values or combinations.
CHECK: Restricts values through a Boolean condition.

A CHECK condition can pass when its result is unknown because of NULL. Combine it with NOT NULL when a value is compulsory.

The following table definitions demonstrate how these constraints work together when creating an order_line table. They assume that the parent tables, sales_order and product, already exist:

1CREATE TABLE order_line (
2    order_id           INT NOT NULL,
3    line_no            INT NOT NULL,
4    product_id         INT NOT NULL,
5    quantity           INT NOT NULL,
6    unit_price_at_sale DECIMAL(12, 2) NOT NULL,
7
8    PRIMARY KEY (order_id, line_no),
9
10    CONSTRAINT fk_order_line_order
11        FOREIGN KEY (order_id)
12        REFERENCES sales_order(order_id),
13
14    CONSTRAINT fk_order_line_product
15        FOREIGN KEY (product_id)
16        REFERENCES product(product_id),
17
18    CONSTRAINT chk_order_line_number
19        CHECK (line_no > 0),
20
21    CONSTRAINT chk_order_line_quantity
22        CHECK (quantity > 0),
23
24    CONSTRAINT chk_order_line_price
25        CHECK (unit_price_at_sale >= 0)
26);

The composite primary key makes both order_id and line_no non-null. Here, sales_order implements the conceptual ORDER table using a name that avoids the SQL keyword ORDER.

Recognize Rules That Need More

A foreign key from ORDER_LINE to ORDER does not require every order to have a line. Enforcing “every finalized order contains at least one line” needs an additional mechanism coordinated with the order-finalization transaction.

Similarly, checking stock availability involves shared state and concurrent requests. It cannot be solved by naming a column AvailableStock.

Validate and Evolve the Schema

A draft schema should be tested against realistic operations before it becomes difficult to change.

Test normal operations: Create customers, place multi-line orders, and retrieve complete purchases.
Test rejected records: Attempt duplicate line identifiers, nonexistent product references, and missing quantities.
Test historical behavior: Change a catalog price and confirm that past sale prices remain unchanged.
Test deletion rules: Decide whether referenced customers and products can be deleted, retained, or marked inactive.
Test required queries: Verify that reports can be produced without guessing missing relationships or double-counting order totals.

Use consistent names and document important assumptions. Separate a logical design decision, such as an order’s identity, from an access optimization, such as an index.

When requirements change, evolve the schema through controlled migrations. Existing data may need cleanup or backfilling before new constraints can be enforced.

Normalization does not guarantee fast queries, and adding indexes does not correct an unclear data model. Evaluate performance using representative workloads after the structure accurately represents the required facts.

Summary

Schema design defines what records mean, how they are identified, and which relationships and values are permitted. Start with requirements and row grain, place attributes according to dependencies, preserve necessary history, and enforce rules through appropriate constraints. Validate the design against realistic queries, updates, deletions, and future changes.