Schema-Design Capstone

3
1

Introduction

Designing a schema involves more than turning a list of fields into tables. The tables must represent the business correctly, preserve relationships, and support the operations the application needs.

This capstone brings those decisions together through a small order-management database. We will identify entities, resolve relationships, choose keys and constraints, and consider the queries that shape the final design.

Begin with Business Rules

Assume the application follows these rules:

  • A customer can place several orders.

  • Each order belongs to one customer.

  • An order can contain several products.

  • A product can appear in many orders.

  • Each order item records a positive quantity and its agreed unit price.

  • A product appears at most once within an order.

These rules define relationships and restrictions before SQL is written.

We will keep payments, shipping, refunds, and inventory outside this example. They can be modeled separately when their requirements are known.

Identify the Tables and Relationships

The main entities are customers, products, and orders.

Orders and products have a many-to-many relationship. An order_items table resolves it and stores information about each product’s participation in an order.

Table

Main responsibility

customers

Customer identity and contact details

products

Current product information

orders

Order ownership and creation time

order_items

Products, quantities, and agreed prices within an order

A customer has many orders. An order has many order items, and a product can be referenced by many order items.

Customer names belong in customers, rather than being repeated as the current customer name in every order.

Define Keys and Basic Constraints

The following SQL illustrates the schema. ID-generation and timestamp details may need adjustment for the chosen database.

CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NOT NULL UNIQUE
);
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
current_price DECIMAL(12, 2) NOT NULL,
CHECK (current_price >= 0)
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL,
unit_price DECIMAL(12, 2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
FOREIGN KEY (product_id)
REFERENCES products(product_id),
CHECK (quantity > 0),
CHECK (unit_price >= 0)
);

The composite primary key prevents the same product from appearing twice in one order, matching our stated rule.

If the application needs separate lines for the same product, for example, different customizations, then an order-line identifier would be more suitable.

DECIMAL represents exact decimal amounts within its declared precision. This example assumes one currency; a multi-currency design needs an explicit currency policy.

Order-management relationship diagram

Order-management relationship diagram

Preserve Historical Facts

The product’s current price and the order item’s unit price serve different purposes.

Suppose a product costs 50.00 when ordered and later changes to 60.00.

Location

Meaning

products.current_price

Price currently offered

order_items.unit_price

Price agreed for that order item

Updating the product catalogue should not change the amount charged for an earlier order.

This is intentional historical storage, rather than accidental duplication. The same reasoning may justify storing an order-time address or product description when the business must preserve it.

For our simplified model, an order’s item subtotal is calculated from quantity × unit_price. Taxes, discounts, shipping, and rounding policies require additional rules.

Current price vs historical price

Current price vs historical price

Choose Deletion Rules Deliberately

Foreign keys protect relationships, but deletion behavior must match the business.

Deleting a customer should not automatically erase financial history merely because related orders exist.

Common choices include:

  • Preventing deletion while referenced records remain.

  • Deactivating products instead of removing them.

  • Applying a defined retention or anonymization policy to customer information.

  • Allowing order-item deletion only within an appropriate order workflow.

The schema above does not request cascading deletion. Exact default behavior and timing vary by database.

Also, a foreign key does not ensure that an order contains at least one item. That rule requires additional enforcement, such as validating the order when it moves from draft to confirmed.

Add Indexes for Important Queries

Suppose customers frequently view their recent orders:

SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
ORDER BY created_at DESC
LIMIT 20;

A candidate index is:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

It groups entries by customer and then creation time.

The primary key on order_items(order_id, product_id) supports identifying an item and commonly helps retrieve an order’s items.

If the application frequently searches for orders containing a particular product, an additional index beginning with product_id may be useful:

CREATE INDEX idx_order_items_product
ON order_items (product_id);

Check existing indexes first. Constraint-related index creation varies by database, and redundant indexes add maintenance costs.

Creating an order commonly requires inserting the order header and its items together.

A local transaction can make these changes atomic. If inserting an item fails, the application can roll back the incomplete operation.

Other rules need their own concurrency-safe handling. For example, reading available stock and later reducing it without appropriate protection can allow competing orders to oversell.

Correct table structure is necessary, but it does not replace transaction and application design.

Review the Finished Schema

Before accepting the design, verify:

  1. Identity: Does each table have a suitable key?

  2. Relationships: Are required references non-null and protected?

  3. Validity: Are quantities, prices, and uniqueness rules enforced?

  4. History: Can changing current data rewrite past facts?

  5. Lifecycle: What happens when records are deleted or deactivated?

  6. Access: Do important queries have suitable access paths?

Test invalid cases as well as successful inserts: duplicate email addresses, nonexistent products, zero quantities, and attempts to remove referenced records.

Summary

A useful schema begins with explicit business rules. Entities become tables, many-to-many relationships become association tables, and keys and constraints protect the resulting structure.

Historical facts, deletion behavior, transactions, and indexes complete the design. Reviewing these decisions together produces a schema that supports application behavior as well as valid data.

CS Core

Read Similar Blogs

Comments0