Real-World Table Design in SQL

Introduction

Real-world table design is the process of converting business requirements into a relational structure that remains correct, secure, understandable, and efficient in production. Knowing how to write CREATE TABLE is only the beginning. A production model must answer what each row means, how it is identified, which relationships are valid, which values are mandatory, what history must survive, and how the schema behaves under growth and change.

This tutorial follows an online shopping system from requirements to physical SQL tables. Customers register, maintain addresses, browse products, place orders, make payments, and receive shipments. The example also introduces inventory, refunds, promotions, audits, concurrency, reporting, and migration concerns that reveal why real data modeling is more than drawing boxes.

REQUIREMENTS -> DOMAIN MODEL -> KEYS AND RULES
             -> PHYSICAL TABLES -> HISTORY
             -> INDEXES AND SECURITY -> TESTING
             -> VERSIONED EVOLUTION

Begin With Domain Questions

Do not immediately create tables from a feature request. Extract business nouns such as customer, address, product, order, order item, payment, and shipment. Then extract actions: a customer maintains addresses, places orders, pays for an order, and receives shipments. Nouns suggest entities; actions expose relationships and lifecycle transitions.

Ask questions before fixing cardinality. Can a customer have many addresses? Can an order contain the same product twice? Are partial payments, refunds, and split shipments allowed? Can prices change? Must an old order preserve its original price and address? Can customers be deleted? These answers directly determine keys, constraints, child tables, history, and delete behavior.

Turn Requirements Into Business Rules

Suppose the business confirms that customer emails and product SKUs are unique, prices cannot be negative, every order belongs to a customer, each item has positive quantity, and old orders must preserve charged prices. It also allows multiple payment attempts and shipments while requiring historical orders to remain available.

Write these rules before writing DDL. Each should eventually map to a constraint, transaction rule, process, or documented decision. The initial relationship model becomes customer to addresses, customer to orders, order to items, item to product, order to payments, and order to shipments. Cardinality is part of the requirement, not a guess made by the database developer.

Design the Customer Table

A customer needs stable identity, name information, email, current account state, and creation time. Email is a business attribute that may change, so a generated customer_id is a safer primary key. If duplicate accounts are prohibited, email still needs a business uniqueness rule.

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_customers_email UNIQUE (email),
    CONSTRAINT chk_customers_status
      CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED'))
);

Email uniqueness needs further analysis: comparison may be case-insensitive, whitespace should be normalized, shared addresses may be permitted, and a multi-tenant product may scope uniqueness by tenant. The database rule must match the real domain rule rather than a simplistic assumption.

Current Customer State and History

The customers.status column answers the current-state question efficiently. It cannot explain when a customer was suspended, who changed the state, or what the previous state was. If those questions matter, add a status-history table with customer, old and new states, change time, actor, and reason.

Current state and event history are complementary. The current column supports ordinary filtering, while append-oriented history supports audit and reconstruction. Keep them consistent within one transaction. Do not expect a generic updated timestamp to substitute for meaningful business history.

Model Customer Addresses as Rows

Columns such as home_address, shipping_address, and office_address limit the count and duplicate structure. A child table allows any reasonable number of addresses and gives each one its own identity, type, validation state, and lifecycle.

CREATE TABLE customer_addresses (
    address_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    address_type VARCHAR(20) NOT NULL,
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    state_region VARCHAR(100),
    postal_code VARCHAR(30),
    country_code CHAR(2) NOT NULL,
    CONSTRAINT fk_address_customer
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

International addresses vary, so state and postal code cannot always share one global mandatory rule. Design for supported countries and validate at the appropriate boundary. A reference table may be preferable when address types are configurable or contain metadata.

Design Products and Stable Identifiers

A product requires internal identity, SKU, name, current price, and current state. SKU has business meaning and should be unique when required, but it may change format, come from an external system, or be reused under some policies. A stable internal product_id keeps references independent of those changes.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_products_sku UNIQUE (sku),
    CONSTRAINT chk_products_price CHECK (current_price >= 0),
    CONSTRAINT chk_products_status
      CHECK (status IN ('ACTIVE','INACTIVE','DISCONTINUED'))
);

Separate Current Price From Charged Price

The product price answers what the product costs now. An order item must answer what the customer paid when the order was placed. If a price rises from 100 to 120, an old order cannot silently change. Therefore products.current_price and order_items.unit_price represent different facts.

This duplication is a deliberate historical snapshot, not a normalization error. Similar reasoning applies to discounts, tax, currency conversion, and shipping charges. Historical financial records must not be recalculated using current catalog values or current business rules.

Design the Order Header

An order needs stable identity, a customer relationship, a business-facing order number, order time, current state, and currency. The internal primary key can remain compact while the separately unique order number follows customer-service and integration requirements.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_number VARCHAR(40) NOT NULL,
    ordered_at TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    CONSTRAINT uq_orders_number UNIQUE (order_number),
    CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT chk_orders_status
      CHECK (status IN ('PENDING','PAID','PROCESSING',
                        'SHIPPED','DELIVERED','CANCELLED'))
);

Customer deletion requires a policy. Historical orders usually must survive for finance, support, and legal reasons, so unrestricted cascading deletion is often wrong. Anonymization, restricted deletion, or retained minimal customer references may fit better.

Model Order Items as a Relationship Entity

Orders and products form a many-to-many relationship resolved by order_items. The relationship has its own facts: quantity, unit price, discount, tax, and fulfillment state. It is therefore a meaningful entity, not merely a technical join.

CREATE TABLE order_items (
    order_item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_item_order
      FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_item_product
      FOREIGN KEY (product_id) REFERENCES products(product_id),
    CONSTRAINT chk_item_quantity CHECK (quantity > 0),
    CONSTRAINT chk_item_price CHECK (unit_price >= 0),
    CONSTRAINT chk_item_discount CHECK (discount_amount >= 0)
);

If a product may appear only once per order, enforce UNIQUE (order_id, product_id). If separate lines can carry different variants, promotions, or fulfillment instructions, that rule would be wrong. Constraint scope follows business meaning.

Make Financial Meaning Explicit

A column named amount is ambiguous. Prefer names such as unit_price, tax_amount, discount_amount, shipping_amount, and refunded_amount. Define whether amounts are before or after tax and discounts, how rounding works, and which currency applies.

Order totals can be calculated from item facts, but a finalized charged total may also be stored as immutable evidence. If stored derived values remain mutable, define exactly how they are synchronized. A discrepancy between a stored total and its lines should be detectable and investigated, not silently accepted.

Model Payments Separately

An order can experience authorization, capture, failure, retry, partial payment, refund, and chargeback events. A few payment columns on orders cannot represent that lifecycle. Each payment attempt should have identity, order, provider, amount, currency, state, external reference, and timestamps.

CREATE TABLE payments (
    payment_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    provider VARCHAR(40) NOT NULL,
    provider_reference VARCHAR(100),
    amount DECIMAL(19,4) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    status VARCHAR(20) NOT NULL,
    requested_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP,
    CONSTRAINT fk_payment_order
      FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT chk_payment_amount CHECK (amount > 0)
);

Use an idempotency key or a suitable UNIQUE provider reference to prevent duplicate processing during retries. Never store sensitive card data casually; rely on approved payment providers and tokenization according to compliance requirements.

Represent Refunds as Their Own Facts

Refunds may be partial, may occur more than once, and may apply to a payment or individual items. A boolean is_refunded loses amount, reason, actor, state, and timing. A refund table supports an auditable lifecycle and links to the original payment or order.

A CHECK can ensure a refund amount is positive, but comparing total refunds with the original captured amount often spans rows and requires transactional service logic. Constraints should enforce what they can reliably express; complex cross-row rules still need robust transaction design rather than no constraints at all.

Design Shipments for Partial Fulfillment

One order may ship in multiple packages, and one shipment may contain only part of an item quantity. Model shipments separately with carrier, tracking number, status, shipped time, and delivery time. Add shipment_items to map shipped quantities to order items.

This design answers which products were in each package and supports split fulfillment, reshipment, tracking, and returns. A single tracking number on the order header cannot represent these scenarios. Cardinality should reflect actual operations, not the simplest demo flow.

Preserve the Shipping Address Snapshot

An order must generally preserve the address used at checkout even when the customer later edits or deletes a saved address. Depending only on customer_addresses would rewrite history. Copy required address components into an immutable order-address snapshot or version the address and reference the correct version.

Current profile data and historical transaction data serve different questions. Purposeful snapshots preserve evidence and customer-service context. Document which source created the snapshot and prevent later profile updates from mutating it.

Model Inventory as State and Movement

Inventory often belongs to a product and warehouse pair, making (warehouse_id, product_id) a useful composite key. Current quantity supports fast availability checks, while an append-oriented movement ledger explains receipts, reservations, sales, returns, and adjustments.

CREATE TABLE inventory (
    warehouse_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity_on_hand INTEGER NOT NULL,
    quantity_reserved INTEGER NOT NULL DEFAULT 0,
    version_number BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (warehouse_id, product_id),
    CHECK (quantity_on_hand >= 0),
    CHECK (quantity_reserved >= 0)
);

The current balance can be cached for performance, but movement history supports reconciliation and audit. Update both within a controlled transaction, or use an architecture that can rebuild state from the ledger.

Handle Inventory Concurrency

Two customers can attempt to buy the final unit simultaneously. A correct table shape does not alone prevent overselling. Use atomic conditional updates, row locking, optimistic version checks, reservations, or another concurrency strategy appropriate to the workload.

UPDATE inventory
SET quantity_reserved = quantity_reserved + :requested,
    version_number = version_number + 1
WHERE warehouse_id = :warehouse
  AND product_id = :product
  AND quantity_on_hand - quantity_reserved >= :requested
  AND version_number = :expected_version;

The application must verify the affected-row count. Table design and transaction behavior are connected; testing only sequential happy paths will miss critical production failures.

Categories, Variants, and Flexible Attributes

Products can belong to multiple categories, so use a junction table rather than category1 and category2. A category hierarchy can use a self-referencing parent key, though additional logic may be needed to prevent cycles.

Separate a conceptual product from sellable variants when size, color, or configuration creates distinct SKUs, prices, or inventory. Core searchable facts should remain typed relational columns. JSON can hold controlled sparse attributes, but putting all product data in JSON weakens constraints, uniqueness, joins, and discoverability.

Model Reviews and Wishlists With Scoped Rules

A product review belongs to both a customer and a product and carries rating, title, content, moderation state, and creation time. A CHECK constraint can restrict a rating to a range such as one through five. If one customer can submit only one review per product, enforce UNIQUE (customer_id, product_id). If reviews are allowed per verified purchase, the unique scope may also include an order item.

A wishlist is another relationship rather than a numbered set of columns. A composite key on customer and product can prevent duplicate entries, while added_at preserves useful behavior data. Business-rule scope must be identified before choosing a key.

Separate Shopping Carts From Orders

A cart is mutable, temporary commercial intent; an order is a committed historical transaction. Use cart and cart-item tables with ownership, state, timestamps, quantities, and selected variants. Do not treat a cart row as a completed order merely by changing one flag if the two lifecycles, retention rules, and integrity requirements differ.

Cart prices can be estimates because promotions, availability, tax, and shipping may change before checkout. At order creation, validate the cart again and copy finalized commercial facts into the order within a controlled transaction. Preserve which cart produced the order when lineage helps support and analytics.

Promotions and Taxes Need History

Promotion definitions and tax rules change. An order should record which promotion was applied and the resulting discount, not recalculate the old order from today's campaign. Tax records should preserve jurisdiction, rate, taxable basis, and charged amount when audit or invoicing requires them.

Current reference rules support new calculations; transaction snapshots preserve completed results. This same distinction appears throughout real schemas: master data describes now, while business events record what happened then.

Store Status History and Business Events

A current order status is convenient, but status history records transitions with time, actor, source, and reason. Some milestones also deserve explicit timestamps such as paid_at, cancelled_at, and delivered_at when they are important independent facts.

A status label should not replace every event. Event tables can represent retries and repeated transitions that one column cannot. Decide whether history is mutable correction data or append-only evidence, and enforce the chosen policy.

Apply the Pattern Beyond E-Commerce

The same reasoning applies to other domains. An employee has a current department and salary, while assignment and salary-history tables preserve effective periods. A bank stores immutable transactions and may cache a balance. A school models enrollment as an entity carrying term, status, and final grade. Healthcare records require carefully separated patient identity, encounters, observations, and protected audit history.

Real relationships are often rich entities with dates, states, amounts, actors, and provenance. When a relationship gains its own lifecycle or attributes, model it explicitly rather than hiding those facts in either parent table.

Design Effective-Dated Records

Salary, pricing agreements, subscriptions, assignments, and insurance coverage often have a validity interval. Store effective_from and effective_to with an explicit convention for open-ended periods and boundary inclusion. A CHECK can require the end to follow the start, but preventing overlapping periods for the same owner may require an exclusion constraint, transaction logic, or serialized update process depending on the DBMS.

Queries must specify whether they ask for the current row or the row valid at a historical instant. Consistent time zones and precision matter at interval boundaries. Never overwrite an old effective row when its history is contractually significant.

Use Ledger Principles for Financial Events

Banking, wallet, and accounting systems benefit from immutable transaction entries rather than repeatedly replacing a balance with no explanation. Each event records identity, account, type, amount, currency, effective time, posting time, status, reference, and idempotency key. Reversals create compensating records rather than deleting evidence.

A current balance may be cached for fast reads, but it must reconcile with the authoritative entries under a documented rule. Double-entry models go further by requiring balanced postings across accounts. Financial table design demands transactional consistency, precision, auditability, and strict permissions beyond an ordinary numeric column.

Understand Minimum Cardinality Limits

A foreign key can ensure that every order item references an order, but it does not by itself guarantee that every order contains at least one item. That minimum-child rule crosses rows and timing boundaries: the order may need to exist briefly before its items are inserted.

Enforce the complete invariant at an appropriate transaction boundary. A checkout operation can insert the order and items atomically, then reject an empty order before commit. Deferred constraints, procedures, or controlled state transitions may help on some platforms. The inability to express every rule as a simple CHECK is not a reason to abandon strong constraints for the rules that are expressible.

Build Operational Observability Into the Model

Production diagnosis often depends on fields omitted from simplistic designs. Correlation IDs, idempotency keys, source system, import batch, created and updated timestamps, actor identity, and processing state can connect database records to messages, logs, and external providers. Add them when they have clear operational meaning rather than as unexplained generic metadata.

Use specific names and indexes aligned with support workflows. Define retention because diagnostic identifiers can still be sensitive. Observability columns should help answer what happened, where the record came from, whether a retry is duplicate, and which process owns recovery without becoming an ungoverned dumping ground.

Protect Multi-Tenant Ownership

In a SaaS system, tenant scope is part of business identity. An email, SKU, or order number may be unique within a tenant rather than globally. Include tenant_id in the relevant UNIQUE rules and indexes.

Relationships must also prevent cross-tenant references. Composite foreign keys or other enforcement can ensure an order and customer belong to the same tenant. Application filters alone are fragile because one missing predicate can expose another customer's data. Tenant isolation must be designed, constrained, authorized, and tested.

Separate Audit Logs From Business History

Business history explains domain changes such as order transitions or salary periods. A technical audit log records who changed which fields, through what channel, and when. They may overlap, but they serve different consumers and retention policies.

Audit records should be difficult to modify and may belong in separately protected storage. Capture useful context without copying secrets or unnecessary personal data. Decide whether audit needs before-and-after values, request identifiers, source system, and correlation IDs.

Design Security Into the Schema

Minimize sensitive data before considering encryption. Store password hashes produced by an approved authentication process, never plaintext passwords. Prefer provider tokens over card details and avoid copying identity documents or secrets into logs and generic metadata.

Classify columns, restrict privileges by ownership, encrypt where required, define masking and retention, and consider separation for especially sensitive data. Encryption does not replace authorization. External identities should map through provider and external subject values rather than overloading internal primary keys.

Support External Systems and Idempotent Imports

Integrations need lineage. Store source system, external identifier, import batch, and timestamps where relevant. A UNIQUE rule on (source_system, external_id) can stop repeated messages from creating duplicate entities and makes retries safe.

Raw staging tables may intentionally mirror incoming data and accept incomplete rows so validation can report errors. Core production tables should enforce strong types and business rules. Do not copy a loose staging shape directly into the operational model.

Keep OLTP and Reporting Models Distinct

The transactional schema is optimized for correct writes, ownership, and current operations. Analytical workloads may prefer facts, dimensions, precomputed measures, and historical snapshots. Distorting every OLTP table to avoid report joins can damage integrity and ordinary application performance.

Use replicas, ETL, views, materialized summaries, or a warehouse when reporting requirements differ significantly. One database model does not fit every workload. Preserve a clear source of truth and define how analytical data is refreshed and reconciled.

Choose Indexes From Query Patterns

Primary and UNIQUE constraints commonly create indexes, but frequent access paths may require more. Customer order history might use (customer_id, ordered_at); payment reconciliation may use provider and provider reference; fulfillment may filter status and ordered time.

Composite index order matters. Low-cardinality status alone may not be selective, but it can participate in an index aligned with a real query. Every index consumes storage and increases write, backup, and maintenance work, so review actual plans and production-like volumes instead of indexing every foreign key or filter mechanically.

Plan Scale, Retention, and Partitioning

Estimate parent and child growth separately. One million orders may create several million items, payment attempts, status events, and inventory movements. Include index size, not only table rows, in capacity estimates.

Define how long operational, financial, audit, and personal data remain hot, archived, or deleted. Partitioning can help very large time-oriented tables when the partition key supports pruning and lifecycle operations, but it does not replace indexes or fix poor queries. Backups, restore testing, and archival are part of production design; replication alone is not backup.

Use Clear Naming and Documentation

Choose names that communicate meaning: ordered_at, delivered_at, amount_usd, or weight_kg are clearer than date1, value, and flag. Apply a consistent convention for tables, keys, booleans, timestamps, indexes, and constraints.

Maintain an ER diagram and data dictionary describing ownership, source, NULL meaning, units, sensitive classification, and non-obvious rules. Documentation does not replace constraints, but it preserves decisions that cannot be inferred from DDL alone.

Evolve Tables Through Versioned Migrations

Production schemas change. Store migration files with application code, review them, and test against realistic data. Avoid ORM-driven automatic mutation in production and never make manual changes that leave environments inconsistent.

Use expand-and-contract changes when old and new application versions overlap: add the new structure, support both representations, backfill safely, switch readers and writers, verify, then remove the old structure later. Migration cost is part of design, especially for key, type, partition, and high-volume changes.

Test the Model With Real Scenarios

Test duplicate email and SKU, invalid prices, missing parents, zero quantities, partial payments, retries, split shipments, refund limits, address edits after ordering, concurrent inventory reservations, status transitions, tenant boundaries, deletion, and restoration. Verify expected failures as carefully as successful inserts.

Test data growth, representative query plans, transaction rollback, deadlocks, migration duration, and recovery. A model that only works with a few clean sample rows is not production-ready. QA should validate constraints and historical meaning, not only application screens.

Common Real-World Design Mistakes

  • Creating tables directly from UI forms, API payloads, or ORM classes.
  • Using mutable email, SKU, or phone values as permanent identity.
  • Adding surrogate keys without enforcing business uniqueness.
  • Storing repeating addresses, categories, or products in numbered columns.
  • Recalculating old orders from current prices, taxes, or addresses.
  • Representing payments, shipments, refunds, and history as simple flags.
  • Using cascade deletion without ownership and retention analysis.
  • Relying only on front-end validation or tenant filtering.
  • Indexing every column without measuring reads and write cost.
  • Applying a transactional model unchanged to analytics or staging.
  • Ignoring concurrency, idempotency, growth, retention, and restore behavior.
  • Changing production schemas outside version-controlled migrations.

Production Design Checklist

  • Confirm entities, actions, cardinalities, ownership, and lifecycle with the business.
  • Give each important entity stable identity and enforce scoped business keys.
  • Choose semantic types, precision, units, time-zone behavior, and NULL rules.
  • Use constraints for mandatory values, domains, relationships, and valid ranges.
  • Separate current state, business history, technical audit, and historical snapshots.
  • Model payments, shipments, refunds, promotions, and inventory as explicit facts.
  • Design transaction and concurrency behavior for multi-row operations.
  • Minimize sensitive data and enforce tenant-safe ownership.
  • Build indexes from representative queries and expected scale.
  • Define retention, archival, deletion, backup, and recovery policies.
  • Keep staging, OLTP, and analytical responsibilities distinct.
  • Deploy schema evolution through tested, version-controlled migrations.

Interview Questions and Answers

Why is email usually not the customer primary key? It can change, while references need stable identity; email can still have a UNIQUE business rule.

Why store unit price in order items? It preserves the historical price actually charged when the product's current price changes.

Why separate payments from orders? One order can have multiple attempts, captures, refunds, and provider events with independent lifecycles.

How do you support split shipments? Use shipments and shipment items so each package can contain selected order-item quantities.

What is the current-state-plus-ledger pattern? Keep an efficient current balance or status while preserving append-oriented events that explain and reconcile it.

Why is tenant ID part of design? Uniqueness, relationships, indexes, authorization, and isolation often operate within tenant scope.

Should reports use the OLTP model directly? Simple reports can, but heavy analytics may require replicas, summaries, or a warehouse designed for that workload.

What makes a migration production-ready? It is versioned, backward-compatible where needed, tested at realistic volume, observable, and supported by a recovery plan.

Key Takeaway

Real-world SQL table design connects business meaning to enforceable data structures. The successful model does not merely store customers and orders; it preserves identity, prices, addresses, payments, shipments, inventory movements, state changes, ownership, and history while remaining operable under concurrency and growth.

PRODUCTION TABLE DESIGN = BUSINESS RULES
                        + CORRECT CARDINALITY
                        + STABLE IDENTITY
                        + ENFORCED INTEGRITY
                        + HISTORICAL MEANING
                        + TRANSACTION SAFETY
                        + SECURITY AND RETENTION
                        + WORKLOAD-AWARE INDEXES
                        + VERSIONED EVOLUTION

Start from scenarios, not screens. Distinguish current master data from historical transaction facts, enforce every rule the database can reliably protect, and test failure paths as seriously as successful ones. A strong schema continues to explain what happened long after the original application code has changed.