Real-World Database Design in SQL

Introduction

Real-world database design converts business operations into a structure that preserves meaning under everyday use, growth, failure, and change. It is not limited to drawing tables or choosing a primary key. A production design must express ownership, relationships, history, constraints, transactions, access paths, security, recovery, and migration behavior.

This tutorial follows an e-commerce example because it contains common modeling problems: customers place orders, orders contain products, payments have independent lifecycles, and inventory changes concurrently. The goal is not one universal schema. It is a repeatable reasoning process that connects requirements to SQL and remains understandable when business rules evolve.

What Is Real-World Database Design?

Database design is the process of identifying business facts, grouping them into entities and relationships, selecting keys and data types, and enforcing rules with relational constraints. Real-world design adds workload and operational concerns: query latency, concurrency, auditing, privacy, backup, versioning, and scale.

A good schema makes valid states easy to store and invalid states difficult or impossible. Each table has a clear grain, each current fact has an authoritative owner, and relationships are explicit. Applications can evolve without rewriting data merely because presentation needs changed.

Why Database Design Matters

Poor design causes duplicated facts, modification anomalies, orphaned rows, incorrect totals, and fragile reports. As data grows, unclear ownership spreads into application code and makes every new feature expensive. Indexes and hardware cannot repair ambiguous semantics.

A strong design reduces redundancy, enforces consistency, supports efficient queries, and provides stable integration boundaries. It also lowers migration risk because developers can explain what every row represents and which rules must remain true during change.

The Design Process

  1. Gather business workflows, terminology, rules, and reporting needs.
  2. Identify entities, events, attributes, and relationship grains.
  3. Choose candidate keys and model cardinality and optionality.
  4. Normalize dependencies and define historical requirements.
  5. Select data types and implement declarative constraints.
  6. Design transactions, concurrency controls, and indexes.
  7. Plan security, auditing, backup, migration, and monitoring.
  8. Test with realistic volume and refine from measured behavior.

The process is iterative. New requirements may reveal a missing entity, a different relationship grain, or a temporal rule that changes an earlier assumption.

Understanding Business Requirements

Begin with business actions, not table names. An online store may register customers, manage product catalogs, place and cancel orders, authorize payments, reserve stock, process refunds, and report sales. Each action has inputs, rules, state transitions, and failure behavior.

Ask precise questions. Can guests order? Can one product belong to several categories? Can an order have multiple payments? Is product price historical? Can stock become negative? Are orders deleted or retained? Requirements should include exceptions, concurrency, retention, privacy, and reporting, not only the happy path.

Identify Entities and Events

Entities have identity and lifecycle: customer, product, category, order, payment, warehouse. Relationship entities capture meaningful associations: order item, product category, inventory balance. Events such as payment attempt, stock movement, or order status change may require immutable history rather than one mutable column.

A noun is not automatically a table, and every table should not become an entity. Address may be an owned value, a historical snapshot, or a reusable entity depending on the rules. Model the business meaning rather than the user-interface form.

Define Table Grain

Grain states exactly what one row represents. One orders row represents one placed order. One order_items row represents one product line in one order. One inventory row may represent current stock for one product at one warehouse. Without explicit grain, duplicate keys and inaccurate aggregation are inevitable.

Write the grain in plain language before selecting columns. Every non-key attribute should describe that grain. If a column describes another entity or a different time scope, move it or model the relationship explicitly.

Identify Attributes

Attributes should be atomic for intended operations and belong to their entity. A customer may own email and display name; an order owns placement time and status; an order item owns quantity and transaction-time unit price. Product current price does not replace the price agreed on an old order.

Distinguish required, optional, derived, current, and historical attributes. Avoid storing comma-separated collections, overloaded status text, and calculated values without a maintenance plan. Record units and semantics for money, measurements, and time.

Choosing Keys

A candidate key uniquely identifies a row and is minimal. Natural keys come from the domain, such as a standardized code; surrogate keys are generated identifiers. Surrogates simplify references and remain stable when business identifiers change, but they do not replace uniqueness constraints on true business keys.

Use key types consistently and keep them narrow enough for foreign keys and indexes. Avoid exposing sequential internal identifiers when that creates security or coupling concerns. Public identifiers can be separate from internal relational keys.

Natural vs Surrogate Keys

AspectNatural keySurrogate key
SourceBusiness domainDatabase or application
MeaningBusiness-readableUsually meaningless
StabilityMay change with rulesDesigned to remain stable
WidthMay be composite or textUsually compact
UniquenessEnforces business identityEnforces row identity only

A practical pattern uses a surrogate primary key plus a unique constraint on the stable business key. This permits efficient references without allowing duplicate real-world entities.

Model Relationships and Cardinality

Relationships define how entity instances connect. One customer can place many orders; each order belongs to one customer. One order contains many items, while one product can appear in many orders. The order-item junction resolves that many-to-many relationship and stores quantity and purchase price.

Cardinality must include optionality. An order may exist before a payment succeeds, and a customer may have no orders. Foreign-key nullability, uniqueness, and delete behavior should reflect those rules rather than framework defaults.

One-to-One Relationships

A one-to-one relationship can be represented in one table or two tables linked by a unique foreign key. Separate tables are useful when data has a different lifecycle, access policy, size, or optionality, such as customer credentials and a large optional profile.

Do not split tables merely to reduce column count. A true one-to-one extension should have a reason, and the database must enforce uniqueness on the foreign key.

One-to-Many Relationships

Place the foreign key on the many side. Orders contain customer_id; products may contain one category_id if the rule permits exactly one category. Index frequently joined foreign keys when access paths require it.

Choose delete behavior deliberately. Restrict deleting a customer with orders, preserve orders while anonymizing personal data, or cascade only when child rows have no independent retention requirement.

Many-to-Many Relationships

Use an association table whose grain is one relationship. Order items link orders and products and own relationship attributes such as quantity, unit price, discount, and tax. A composite key or unique constraint prevents duplicate line identity according to business rules.

If the relationship has lifecycle or references of its own, a surrogate key may be useful, but retain business uniqueness. Many-to-many tables are first-class models, not implementation leftovers.

Self-Referencing Relationships

Hierarchies can use a foreign key that references the same table, such as category parent or employee manager. Define root representation, cycle prevention, maximum depth expectations, and subtree query needs. An adjacency list is simple, while closure tables or materialized paths can optimize complex hierarchy operations.

Recursive relationships need careful deletion behavior. Cascading an entire category subtree may be dangerous, while restriction or explicit reparenting makes the operation visible.

Conceptual, Logical, and Physical Models

ModelFocusTypical output
ConceptualBusiness concepts and major relationshipsHigh-level entity diagram
LogicalAttributes, keys, cardinality, dependenciesNormalized relational model
PhysicalDatabase-specific implementationTypes, indexes, partitions, constraints

Separating these levels prevents vendor features from obscuring business reasoning. An ERD should show identifiers, relationship cardinality, optionality, and important constraints, then remain synchronized with migrations.

Normalization and Dependencies

Normalization places facts with the keys that determine them. First Normal Form removes repeating groups; Second and Third Normal Forms address partial and transitive dependencies; BCNF strengthens determinant rules; later forms address multivalued and join dependencies.

Normalize the authoritative model far enough to prevent harmful anomalies. Then denormalize only measured read patterns with explicit synchronization. Historical snapshots such as order-time product name and price are legitimate transaction facts, not accidental duplication.

Choose Appropriate Data Types

Use exact numeric types for money, integers sized for realistic growth, date/time types for temporal values, and bounded text where limits are meaningful. Do not store dates or numbers as text. Record currency separately rather than assuming one currency globally.

Time requires a policy. Store event instants in a consistent time zone, preserve local business date where it matters, and avoid ambiguous timestamps. Data-type portability differs among MySQL, PostgreSQL, SQL Server, Oracle, and SQLite, so physical design must match the chosen engine.

Use Declarative Constraints

NOT NULL expresses required data, UNIQUE protects business identity, CHECK constrains domains, and foreign keys preserve references. Defaults should represent a valid business default, not hide missing input. A default status of pending may be valid; a default birth date is not.

Constraints protect every write path, including imports and administrative scripts. Application validation improves messages and workflow, but it cannot replace database guarantees under concurrency.

E-Commerce Core Schema

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    email       VARCHAR(320) NOT NULL UNIQUE,
    full_name   VARCHAR(150) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    category_id BIGINT PRIMARY KEY,
    name        VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id   BIGINT PRIMARY KEY,
    category_id  BIGINT NOT NULL,
    sku          VARCHAR(80) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(14, 2) NOT NULL CHECK (current_price >= 0),
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Customer email and product SKU are protected as business keys even though surrogate identifiers are used for references. Product price is current catalog state; order items will preserve transaction-time price separately.

Orders and Order Items

CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    placed_at    TIMESTAMP NOT NULL,
    version_no   INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CHECK (order_status IN ('PENDING','PAID','SHIPPED','CANCELLED'))
);

CREATE TABLE order_items (
    order_id     BIGINT NOT NULL,
    line_no      INTEGER NOT NULL CHECK (line_no > 0),
    product_id   BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   DECIMAL(14, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, line_no),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Product name and unit price are order-time snapshots. They intentionally preserve what was sold even if the catalog later changes. The order currency defines how item monetary values are interpreted.

Payments and Payment Attempts

An order can have several payment attempts, partial captures, or refunds, so payment should not be one status column on the order. Model payment records with provider reference, amount, currency, status, timestamps, and idempotency key. Use uniqueness to prevent one provider event from being processed twice.

Order payment state can be derived or maintained as a projection, but the payment history remains authoritative for reconciliation. Never store raw card secrets that belong with a payment provider; retain only approved tokens and audit-safe identifiers.

Querying Orders

SELECT
    o.order_id,
    o.placed_at,
    o.order_status,
    c.full_name,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
WHERE o.customer_id = :customer_id
GROUP BY o.order_id, o.placed_at, o.order_status, c.full_name
ORDER BY o.placed_at DESC;

The grouping preserves one row per order. Joining payments directly to items in the same query could multiply rows, so aggregate each one-to-many child to order grain before combining multiple collections.

Designing Indexes

Indexes follow access patterns. A customer order history may need orders(customer_id, placed_at). Product browsing may need category and active status. Payment reconciliation may search provider reference. Primary and unique constraints already create useful indexes in most engines.

Column order matters in composite indexes, and every additional index increases write and storage cost. Use execution plans, realistic cardinality, and latency measurements. Avoid indexing low-selectivity columns alone unless the database can use them effectively with another condition.

Transactions and ACID

Order placement may create an order, add items, reserve stock, and record an outbox event. These operations must either commit together or roll back together. Atomicity prevents partial orders, consistency enforces rules, isolation controls concurrent interference, and durability preserves committed results.

Keep transactions short and deterministic. Do not hold database locks while waiting on remote payment or email services. Commit local state and coordinate external work through idempotent workflows and reliable events.

Concurrency and Inventory

Inventory is a concurrency problem, not just a quantity column. Two buyers can read the same available value and both reserve it. Use atomic conditional updates, row locks, serializable transactions, or optimistic version checks according to contention and engine behavior.

UPDATE inventory
SET reserved_quantity = reserved_quantity + :quantity,
    version_no = version_no + 1
WHERE product_id = :product_id
  AND warehouse_id = :warehouse_id
  AND on_hand_quantity - reserved_quantity >= :quantity;

The application must verify that exactly one row was updated. A stock-movement ledger can provide auditability, while a maintained balance supports fast availability reads.

Audit Columns and History

Common audit fields include created timestamp, updated timestamp, actor identifier, and version number. They help operations but do not provide complete history because updates overwrite old values. Regulated or high-risk domains may need temporal tables, append-only events, or explicit history relations.

Audit timestamps should be database-controlled where possible and use a consistent clock. Record why a change occurred, not only when, when business investigation requires that context.

Soft Delete vs Hard Delete

Hard deletion removes a row. Soft deletion marks it inactive or records a deletion timestamp. Soft delete supports recovery and history but complicates every uniqueness rule, foreign key, query, and index. It should not be a universal default.

Orders may need retention, while abandoned temporary data may be safely deleted. Privacy requirements can demand anonymization or physical removal. Define policies per entity and ensure all ordinary queries apply the intended visibility rule.

Status Design

Status values represent a state machine, not decorative labels. Define allowed transitions such as pending to paid, paid to shipped, and pending to cancelled. A check constraint validates the domain but not every transition; application logic, procedures, or event workflows may enforce transitions.

Use lookup tables when statuses have managed metadata, translations, or frequent administrative change. Use checks when the set is small, stable, and code-controlled. Record status history when timing and actor matter.

Monetary Values and Multiple Currencies

Use exact decimal types and store an ISO currency code with monetary amounts. Define precision, rounding, tax order, discount allocation, and refund behavior. Never aggregate different currencies without conversion.

Currency conversion needs source currency, target currency, exchange rate, provider, and effective time. Order-time conversions are historical facts and should not be recalculated using today's rate unless the business explicitly requests restatement.

Designing for Scale

Estimate volume, growth, retention, peak throughput, and query distribution. Large tables may need partitioning by date or tenant, archival, read replicas, or specialized analytical storage. Partition keys should support pruning and operational maintenance without creating hotspots.

Do not partition or shard prematurely. A well-indexed relational database can handle substantial workloads. Sharding introduces distributed transactions, global uniqueness, routing, and rebalancing complexity. Measure first and preserve a clear ownership boundary.

Denormalization and Read Models

Normalize the authoritative transactional model, then add summary tables, materialized views, counters, search documents, or API projections for measured read needs. Every copy needs a source, freshness target, update mechanism, reconciliation, and rebuild path.

Historical snapshots are different from current-value caches. Name them clearly so synchronization does not rewrite transaction history. Denormalization should solve a demonstrated access problem, not compensate for missing indexes or poor SQL.

Security Design

Apply least privilege through separate application roles, restricted administrative access, parameterized SQL, and controlled migration accounts. Encrypt network connections and sensitive storage, manage secrets outside source code, and audit privileged actions.

Minimize sensitive data and separate it when access requirements differ. Row- or column-level security may protect tenant or personal information, but application authorization remains necessary. Test that backups, replicas, exports, logs, and read models follow the same policy.

Backup and Recovery

A backup strategy is defined by recovery point objective and recovery time objective. Full backups, incremental backups, transaction logs, snapshots, and replicas serve different purposes. A replica is not a backup because corruption or accidental deletion can replicate immediately.

Restore tests are essential. Document dependencies, encryption keys, retention, off-site storage, and point-in-time recovery. Verify that recovery includes schema, data, users, jobs, and external projections required for service operation.

Schema Versioning and Migrations

Store ordered migrations in version control and make deployments repeatable. Favor backward-compatible expansion and contraction: add new structures, deploy code that can use both versions, backfill safely, switch reads and writes, then remove old structures later.

Large changes need batching, checkpoints, throttling, observability, and rollback planning. Avoid long blocking table rewrites during peak traffic. Test migrations against production-like volume and verify data invariants afterward.

Naming Conventions

Use consistent singular or plural table naming, predictable primary and foreign key names, and readable constraint and index names. Avoid reserved words, unexplained abbreviations, and case-sensitive quoted identifiers unless required.

Names should communicate grain and time semantics. unit_price_at_order is clearer than price_copy; deleted_at is clearer than a generic flag. Consistency improves SQL review, tooling, and incident response.

Multi-Tenant Database Design

Software-as-a-service systems must define tenant isolation early. A shared schema commonly adds tenant_id to tenant-owned tables and includes it in business uniqueness, foreign-key relationships, indexes, and query predicates. A unique product SKU may be unique within a tenant rather than across the entire platform. Foreign keys that include tenant identifier help prevent one tenant's child row from referencing another tenant's parent.

Separate schemas or databases provide stronger operational isolation but increase migration, connection, monitoring, and capacity-management work. The choice depends on regulatory requirements, noisy-neighbor risk, tenant size, customization, and recovery needs. Row-level security can reinforce shared-table isolation, but tests must verify every administrative and background path. Never rely only on a user-supplied tenant filter in application code.

Service and API Boundaries

A database schema should support domain ownership without becoming a public integration API. When several services write directly to the same tables, constraints and migrations become coupled to every deployment. Prefer one owning service or controlled data-access boundary for each domain, and expose stable contracts for consumers.

Cross-domain reports may use read replicas, warehouses, change streams, or approved views rather than granting broad transactional access. Avoid distributed foreign keys that a database cannot enforce. Store remote identifiers and coordinate workflows with idempotent commands, outbox events, and reconciliation. Service boundaries do not eliminate relational modeling; each service still needs clear grain, keys, constraints, and history within its owned data.

Observability and Capacity Planning

Production design includes the ability to see how the database behaves. Monitor query latency percentiles, throughput, deadlocks, lock waits, connection usage, cache hit ratio, replication lag, storage growth, backup age, and failed migrations. Capture execution plans for important queries and track plan regressions after data distribution or schema changes.

Capacity plans should estimate row growth, index size, transaction-log volume, retention, peak concurrency, and restore duration. Alert before storage or identifier ranges are exhausted. Operational metrics should connect to business grain: orders per minute, payment retry rate, inventory conflicts, and delayed outbox events often explain database symptoms better than infrastructure metrics alone. Review forecasts and load tests whenever major product behavior changes.

Testing the Database Design

Test positive and negative constraints, transaction rollback, concurrent inventory updates, duplicate idempotency keys, invalid state transitions, and delete behavior. Verify reporting grain and totals with representative fixtures. Load tests should use realistic skew, not uniformly distributed toy data.

Migration tests compare source and target counts, keys, sums, and exceptional rows. Recovery tests restore backups and replay required events. Database quality includes behavior during failure, not only successful inserts and selects.

Common Design Mistakes

  • Creating tables directly from screens without defining business grain.
  • Using one wide table for unrelated entities and lifecycles.
  • Adding surrogate keys while omitting business uniqueness.
  • Storing collections, dates, or numbers as unvalidated text.
  • Relying only on application validation instead of constraints.
  • Using cascade delete without analyzing retention and ownership.
  • Joining several one-to-many relations and inflating totals.
  • Storing current catalog values instead of transaction snapshots.
  • Adding indexes or denormalization without workload measurements.
  • Ignoring concurrency, security, migration, backup, and recovery.

Best Practices

Model business meaning before physical optimization. State table grain, identify candidate keys, document cardinality and optionality, normalize dependencies, and enforce rules declaratively. Preserve historical values deliberately and keep current facts authoritative.

Design queries, transactions, indexes, and concurrency together. Minimize sensitive data, version every schema change, test realistic failure scenarios, and monitor production behavior. Keep diagrams and documentation synchronized with migrations, and revisit assumptions when business rules change.

Interview Questions and Answers

What is real-world database design?

It is the translation of business rules and workloads into a reliable, constrained, secure, and maintainable database.

What should be defined before columns?

Define the entity or relationship grain and the business events the table must support.

Why use a surrogate key with a unique constraint?

The surrogate gives stable compact references, while the unique constraint protects real business identity.

Why store unit price on an order item?

It preserves the transaction-time price even when the catalog price changes.

How do you prevent overselling?

Use transactional atomic updates, locks, or optimistic versions and verify the affected row count.

When should denormalization be used?

Use it for a measured access need with explicit ownership, synchronization, reconciliation, and rebuild controls.

Why are restore tests necessary?

A backup is useful only when the complete service state can be recovered within its objectives.

Practical Exercises

  1. Write the grain of every table in the e-commerce example.
  2. Model products that belong to several categories.
  3. Add addresses with current and order-time snapshot semantics.
  4. Design payment attempts and refunds with idempotency.
  5. Create indexes for customer history and product browsing.
  6. Prevent concurrent stock reservations from overselling.
  7. Model order status history and allowed transitions.
  8. Design a daily sales projection without changing source ownership.
  9. Plan a zero-downtime migration for a new required column.
  10. Define backup and recovery tests for the complete schema.

Quick Reference

ConceptDesign question
GrainWhat does one row represent?
Candidate keyWhat minimally identifies the row?
CardinalityHow many related instances are allowed?
ConstraintWhich invalid states can the database reject?
SnapshotWhich historical value must remain unchanged?
TransactionWhich changes must commit together?
IndexWhich measured access path needs support?
RecoveryHow will service state be restored?

Complete Database Design Workflow

  1. Collect workflows, exceptions, reports, retention, and scale expectations.
  2. Build a shared business vocabulary and identify entity lifecycles.
  3. Define table grains, attributes, candidate keys, and relationships.
  4. Normalize dependencies and mark legitimate historical snapshots.
  5. Select types and implement keys, checks, and foreign keys.
  6. Design state transitions, transactions, and concurrency controls.
  7. Create workload-driven indexes and query important reports.
  8. Plan security, audit, privacy, backup, and recovery.
  9. Version migrations and test them with realistic data volume.
  10. Validate constraints, failures, concurrency, reports, and restores.
  11. Deploy incrementally with monitoring and rollback capability.
  12. Review production metrics and evolve the model deliberately.

Key Takeaway

Real-world database design begins with business meaning and continues through production operation. Entities, relationships, keys, dependencies, types, and constraints establish correctness. Transactions, concurrency, indexes, security, migration, and recovery make that correctness durable under actual workload and failure.

The strongest schema is not the one with the most tables or indexes. It is the one whose row grains and ownership are clear, whose invalid states are rejected, whose history is intentional, and whose operational behavior is tested. With that foundation, SQL databases can grow without turning every new requirement into a risky rewrite.