Chapter 13: Database Design in Practice — From Requirements to CREATE TABLE

What you'll learn in this chapter

• Use the bottom-up (starting from a form) and top-down (starting from requirements) approaches as appropriate
• Practice the full flow — requirements → ER diagram → CREATE TABLE — end to end, through two examples: a restaurant order slip and an internal SNS
• Avoid common design pitfalls (over-normalization / an inappropriate primary key / a missing deletion strategy / logical deletion vs. GDPR)

Prerequisites

You should understand Chapter 11: Normalization and Chapter 12: E-R Diagrams.

Two Design Approaches

Approach	Starting point	Good for
Bottom-up	An existing form / screen / spreadsheet	Digitizing an existing business process, replacements
Top-down	A requirements document, a feature list	New systems, starting from abstract requirements

In practice, it's normal to mix both. You'll often go back and forth — "sketch the big picture from requirements, while checking for gaps against an existing form."

Bottom-Up in Practice: A Restaurant Order Slip

The Source Data — A Paper Order Slip

================================
      Hanamaru Restaurant
         Order Slip
================================
Slip Number: 2026-0001
Date/Time: 2026/01/15 19:30
Table: 5
Server: Yamada

--------------------------------
Item              Qty  Price   Amount
--------------------------------
Draft Beer         2    600   1,200
Edamame            1    400     400
Karaage            2    700   1,400
Ramen              1    900     900
Fried Rice         1    800     800
--------------------------------
Subtotal                    4,700
Consumption Tax (10%)         470
--------------------------------
Total                       5,170
================================

Step 1: Extract Data Items

Write out every item on the slip that looks like it could become data.

Step 2: Organize Into Entities

Extract entities based on "is this an independent thing?" and "is it referenced from elsewhere?"

Item	Entity candidate
Slip number, date/time, table, server	Order (orders)
Item name, unit price, category	Product (products)
Quantity, amount	Order line item (order_items)
Table number, seat count	Table (tables)
Server name	Staff (staff)
Subtotal, consumption tax, total	Derivable by computation, so not made into columns (computed on demand with an aggregate query)

Step 3: Build the ER Diagram

Step 4: Expand Into CREATE TABLE Statements

A restaurant ordering system (PostgreSQL)

-- Staff
CREATE TABLE staff (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    hire_date   DATE,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

-- Tables (seating)
CREATE TABLE restaurant_tables (
    id            SERIAL PRIMARY KEY,
    table_number  INTEGER UNIQUE NOT NULL,
    seat_count    INTEGER NOT NULL CHECK (seat_count > 0),
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

-- Product master
CREATE TABLE products (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    unit_price    NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
    category      VARCHAR(50),
    is_available  BOOLEAN NOT NULL DEFAULT TRUE
);

-- Order header
CREATE TABLE orders (
    id              SERIAL PRIMARY KEY,
    order_number    VARCHAR(20) UNIQUE NOT NULL,
    table_id        INTEGER NOT NULL REFERENCES restaurant_tables(id),
    staff_id        INTEGER NOT NULL REFERENCES staff(id),
    order_datetime  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Order line items
CREATE TABLE order_items (
    order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10, 2) NOT NULL,  -- a snapshot of the unit price at order time
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_datetime  ON orders(order_datetime);
CREATE INDEX idx_orders_staff     ON orders(staff_id);

Key points:

• The subtotal, tax, and total aren't stored, since they can be derived by computation: computed with SELECT SUM(quantity * unit_price)
• order_items.unit_price is a snapshot (protecting the order from future price increases in the product master)
• order_items.order_id uses ON DELETE CASCADE (deleting an order deletes its line items too)
• order_items.product_id uses ON DELETE RESTRICT (rejects deleting a product still referenced by an order)

Top-Down in Practice: An Internal SNS

Requirements

• Employees (users) can post text
• A post's visibility is one of: company-wide, group-only, or private
• Posts can have comments and likes
• An employee can belong to multiple groups, and a group can have multiple employees
• A like is a record of "who liked which post, and when"

Building Entities From the Feature List

The entity candidates are: User / Post / Comment / Like / Group / GroupMember.

ER Diagram

CREATE TABLE

Table definitions for an internal SNS (PostgreSQL)

CREATE TABLE users (
    id                SERIAL PRIMARY KEY,
    employee_number   VARCHAR(20) UNIQUE NOT NULL,
    email             VARCHAR(255) UNIQUE NOT NULL,
    password_hash     VARCHAR(255) NOT NULL,
    display_name      VARCHAR(100) NOT NULL,
    department        VARCHAR(100),
    profile_image_url VARCHAR(500),
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE groups (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    description  TEXT,
    is_public    BOOLEAN NOT NULL DEFAULT TRUE,
    created_by   INTEGER NOT NULL REFERENCES users(id),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE group_members (
    group_id   INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role       VARCHAR(20) NOT NULL DEFAULT 'member'
               CHECK (role IN ('admin', 'member')),
    joined_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id)
);

CREATE TABLE posts (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    group_id    INTEGER REFERENCES groups(id) ON DELETE CASCADE,
    content     TEXT NOT NULL,
    visibility  VARCHAR(20) NOT NULL DEFAULT 'public'
                CHECK (visibility IN ('public', 'group', 'private')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_created   ON posts(user_id, created_at DESC);
CREATE INDEX idx_posts_group_created  ON posts(group_id, created_at DESC);

CREATE TABLE comments (
    id          BIGSERIAL PRIMARY KEY,
    post_id     BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    content     TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post  ON comments(post_id, created_at);

CREATE TABLE likes (
    post_id     BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id)
);

Key points:

• likes's primary key is the composite (post_id, user_id) (a user can like a post at most once)
• posts.user_id uses ON DELETE RESTRICT (handle author deletion separately)
• comments.post_id uses ON DELETE CASCADE (deleting a post deletes its comments too)
• idx_posts_user_created (a composite index) speeds up fetching a user's timeline

Design Pitfalls

Pitfall 1: Over-Normalization

An example of "trying to apply normalization theory too strictly and splitting tables unnecessarily."

Over-normalized (avoid this)

-- Splitting the name into a separate table (an unnecessary split)
CREATE TABLE person_names (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL
);

CREATE TABLE persons (
    id        SERIAL PRIMARY KEY,
    name_id   INTEGER NOT NULL REFERENCES person_names(id)
);

An appropriate design

CREATE TABLE persons (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL
);

A "name" is an attribute of a "person," not an independent entity. All you get is more JOINs, slowing down both reads and writes.

A decision rule for normalization

"Promote something to an entity only if it's referenced or managed independently of other tables" is the principle. person_names isn't referenced independently by anyone, so a column on persons is enough.

Pitfall 2: Choosing an Inappropriate Primary Key

Using an email address as the primary key (avoid this)

CREATE TABLE users_bad (
    email VARCHAR(255) PRIMARY KEY,  -- email addresses can change
    name  VARCHAR(100)
);

Changing the email requires rewriting every foreign-key reference to it, breaking down the maintainability of referential integrity. The right answer is to make a surrogate key (SERIAL) the primary key and protect the email with a UNIQUE constraint (the hybrid design from Chapter 10: Key Concepts).

Pitfall 3: No Deletion Strategy

A careless CASCADE (avoid this)

CREATE TABLE orders_dangerous (
    id            SERIAL PRIMARY KEY,
    customer_id   INTEGER REFERENCES customers(id) ON DELETE CASCADE  -- deleting a customer wipes out their orders
);

The moment you delete a customer, their entire order history disappears too, making sales aggregation and tax reporting impossible. It's safer to use RESTRICT and design things so you explicitly decide what happens to orders before deleting a customer.

Pitfall 4: Logical vs. Physical Deletion (Conflicting With GDPR)

"Wanting to keep data around" (to reference past history) and "wanting to completely erase data" (GDPR / the right to erasure under personal-data-protection laws) often conflict.

Strategy	Description	Good for
Physical deletion	Completely removes the row with DELETE	GDPR / personal-data-protection compliance, deleting old logs
Logical deletion (deleted_at)	Keeps the row, just sets a delete flag	Business history, restoring accidental deletions, preserving referential integrity
Archiving	Move to a separate table before removing from production	Needs long-term retention but is rarely accessed

An example that reconciles logical deletion with GDPR

-- A business-level logical-deletion column
CREATE TABLE customers (
    id            SERIAL PRIMARY KEY,
    email         VARCHAR(255) UNIQUE,
    name          VARCHAR(100) NOT NULL,
    is_deleted    BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at    TIMESTAMPTZ,
    -- Anonymization for GDPR deletion requests
    anonymized_at TIMESTAMPTZ
);

-- Logical deletion: hide it from business operations here
UPDATE customers SET is_deleted = TRUE, deleted_at = NOW() WHERE id = 1;

-- Responding to a GDPR deletion request: anonymize personal data
UPDATE customers
SET    email = NULL,
       name = 'Deleted User',
       anonymized_at = NOW()
WHERE  id = 1;

Complying with GDPR and personal-data-protection law

GDPR Article 17 (the right to erasure) requires personal data to be physically deleted or fully anonymized. A logical-deletion flag alone (is_deleted = TRUE) isn't enough in some cases, since personal data remains. If you handle users in the EU, you need a design that nulls out or anonymizes personally identifying information on a deletion request, keeping only what's needed for business statistics. Japan's amended Act on the Protection of Personal Information takes a similar approach in practice.

A Design-Review Checklist

Before handing a design off to implementation, review the following items.

Aspect	What to check
Key design	Every table has a primary key, the hybrid strategy is applied
Normalization	Did you reach third normal form? Is deliberate denormalization documented in a comment?
Constraints	NOT NULL / CHECK / FOREIGN KEY are complete
Performance	Indexes on commonly searched columns; not too many JOINs
Operations	Deletion strategy, backups, and monitoring are accounted for
Naming conventions	Consistent snake_case, plurals, and avoiding abbreviations

Connecting to Domain-Driven Design (DDD)

If you adopt domain-driven design

This guide's design process flows "requirements → ER diagram → table definitions," starting from table design. This is the basic skill a junior engineer needs at the stage of building a business system's DB design from scratch.

If you adopt domain-driven design (DDD) at the implementation phase, the thinking flips: the domain model (classes representing business logic) comes first, and the table becomes the means of persisting the model.

See Laravel × DDD × Clean Architecture, Chapter 14: Domain Models and Table Design for details. Think of it as switching between this DB guide's "fundamentals of table design" and the Laravel guide's "domain models and mapping," depending on the phase you're in.

Exercises

Exercise 1: Build a design from a form

From the following sample invoice, extract entities and build an ER diagram plus CREATE TABLE statements.

======================================
              Invoice
======================================
Issue Date: 2026-06-30
Invoice Number: INV-2026-0042

Bill To:
  Sample Co., Ltd.
  〒100-0001 Chiyoda-ku, Tokyo...
  Contact: Taro Yamada

Bill From:
  Suzuki Trading Co.
  〒530-0001 Osaka City, Osaka...

--------------------------------------
Item            Qty     Price     Amount
--------------------------------------
PC Part A        10    3,000    30,000
PC Part B         5    1,500     7,500
Labor Fee          1   10,000    10,000
--------------------------------------
Subtotal                       47,500
Consumption Tax (10%)            4,750
--------------------------------------
Total                          52,250
======================================

Sample answer

The extracted entities are these five: companies (shared by both the counterparty and your own company), contacts (contact people), products, invoices, invoice_items.

ER diagram:

CREATE TABLE:

An invoicing system

CREATE TABLE companies (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    postal_code  VARCHAR(10),
    address      VARCHAR(500)
);

CREATE TABLE contacts (
    id          SERIAL PRIMARY KEY,
    company_id  INTEGER NOT NULL REFERENCES companies(id),
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(200) NOT NULL,
    default_unit_price  NUMERIC(10, 2) NOT NULL CHECK (default_unit_price >= 0)
);

CREATE TABLE invoices (
    id                     SERIAL PRIMARY KEY,
    invoice_number         VARCHAR(20) UNIQUE NOT NULL,
    issued_at              DATE NOT NULL,
    bill_to_company_id     INTEGER NOT NULL REFERENCES companies(id),
    bill_to_contact_id     INTEGER REFERENCES contacts(id),
    bill_from_company_id   INTEGER NOT NULL REFERENCES companies(id)
);

CREATE TABLE invoice_items (
    invoice_id   INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    line_number  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(10, 2) NOT NULL,  -- a snapshot of the unit price at invoicing time
    PRIMARY KEY (invoice_id, line_number)
);

Key points:

• "Bill from" and "bill to" both use the companies table (your own company and the counterparty are both "companies")
• Aggregate values (subtotal / tax / total) aren't stored (computed with SUM(quantity * unit_price))
• invoice_items.unit_price is stored as a snapshot

Common mistakes:

1. Splitting "bill from" and "bill to" into separate tables: since they share the same "company" attributes, they can be unified
2. Whether it's OK to put something that isn't a product, like "labor fee," into products: fine if you want to manage "services + products" together as a business matter; separate it into a services table if you want to treat them as distinct

Exercise 2: Decide on a deletion strategy

For each of the following tables, decide whether "physical deletion / logical deletion / archiving" should be adopted, along with your reasoning.

1. A user (customer) table
2. An access log table (keeping 3 months' worth is enough)
3. An orders table
4. An e-commerce site's cart (cart_items) table

Sample answer

Table	Recommended strategy	Reason
1. Users	Logical deletion + GDPR-compliant anonymization	You want to keep it for business reasons (referential integrity with order history) / a GDPR deletion request requires nulling out personally identifying information
2. Access logs	Physical deletion (once expired) + archiving	Anything past 3 months hurts performance, so delete it. If there's a legal retention period, archive to something like S3
3. Orders	Logical deletion + long-term retention	Tax law requires 7 years of retention / manage cancellations with a status column, don't physically delete
4. Cart	Physical deletion (oldest first)	These are "unconfirmed shopping candidates" — fine to let them naturally expire past a retention period. Prioritize performance

Additional notes:

• A user's "logical deletion" and "GDPR anonymization" can coexist. Hide the record from business operations with a logical-deletion flag, while responding to a GDPR request with physical deletion/anonymization of personally identifying information
• An order's logical deletion is often expressed as a status change to "cancelled" (status = 'cancelled'). Physically deleting it would break past reports
• It's common to set up automation like "physically delete access logs after 3 months" (cron / pg_cron / the partman extension, etc.)

Common mistakes:

1. Adding a deleted_at column to every table: you should choose based on the use case. A "short-lived" table like a cart doesn't need logical deletion
2. Complying with GDPR using just a logical-deletion flag: a flag alone leaves personal data in place, which isn't enough for a service serving the EU

Summary

Here's a recap of what this chapter covered.

• Bottom-up starts from a form or screen; top-down starts from requirements. In practice, you use both together
• The design flow: extract items → organize into entities → ER diagram → normalize → CREATE TABLE
• Don't store aggregate values (subtotal, total, etc.) — derive them by computation (exception: selective denormalization if performance requires it)
• A snapshot (like the unit price at order time) protects history from future changes to the master
• Over-normalization, an inappropriate primary key, a careless CASCADE, and no plan for GDPR compliance are classic pitfalls
• If you adopt domain-driven design at the implementation phase, see the Laravel guide, Chapter 14

That wraps up Part 2 (Design) of this guide. Starting with the next chapter, we move into the applied section and learn patterns you'll often encounter in practice — many-to-many, authentication/authorization, OAuth, and more.

Next chapter

Chapter 14: Table Design for Many-to-Many Relationships