Requirement-to-Schema Workflow

Database design is not something you start by opening a modeling tool and dragging tables onto a canvas. It starts with a plain-language business requirement, and the job of a designer is to translate that requirement into a precise, unambiguous schema without losing or inventing meaning along the way. Every table, column, and constraint you eventually write should trace back to something a stakeholder actually said or something the system genuinely needs.

This translation happens in repeatable stages: gather the requirement, extract the nouns and verbs it implies, group them into entities and relationships, decide on cardinality and constraints, and only then write the DDL (Data Definition Language) that creates the physical schema. Skipping stages is how you end up with schemas that "sort of" work until an edge case — a customer with two addresses, an order with no payment yet — breaks the assumptions baked into the tables.

Why it matters

Requirements arrive as sentences; a database needs tables. Translating one into the other is a skill, and the best test of a design is whether it can answer the questions the business will ask. A schema that cannot answer them is wrong, however tidy it looks.

The Five-Stage Workflow

A disciplined design process moves through the same stages regardless of the domain, whether you are modeling an e-commerce store or a hospital scheduling system.

• 1. Capture the requirement in plain language — e.g. "Customers place orders. Each order contains one or more products, and each product has a price and a stock quantity."
• 2. Identify entities — nouns that represent things the business needs to remember: Customer, Order, Product.
• 3. Identify relationships and cardinality — how entities connect and how many of one relate to how many of another (a customer places many orders; an order contains many products, and a product can appear on many orders).
• 4. Define attributes and constraints — what data each entity holds, which fields are required, which must be unique, and which reference other entities.
• 5. Translate to DDL and validate against real queries — write the CREATE TABLE statements, then mentally (or actually) run the application's most common queries against the design to confirm it can answer them without contortion.

Working Backward from Questions the System Must Answer

A requirement sentence tells you what the business does, but it rarely tells you what questions the application needs to answer quickly. "Show me all orders a customer placed in the last 30 days" or "what is the total revenue per product this month" are the real tests of a schema. As you move through the workflow, keep a running list of the queries the application will need, because a schema that satisfies the requirement narrative but cannot answer these questions efficiently will need to be reworked before it reaches production — better to catch that during design than after data has accumulated in a live table.

Requirement Ambiguity Is a Design Problem, Not a Detail to Skip

Real requirements are almost always incomplete. "Each order contains one or more products" says nothing about whether the same product can appear twice on one order at different quantities, or whether an order can exist with zero products while it is still a draft. A designer's job is to surface these ambiguities and get them resolved — either by asking the stakeholder or by making an explicit, documented assumption — before they get silently encoded as an accidental constraint in the schema.

Example

-- Requirement: "Customers place orders. Each order contains one or more
-- products, and each product has a price and a stock quantity."

-- Step 2 & 3 identified entities: Customer, Order, Product
-- Step 3 identified relationships: Customer 1---* Order, Order *---* Product

-- Step 5: translate to DDL
CREATE TABLE customers (
    id            BIGINT PRIMARY KEY,
    full_name     VARCHAR(150) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    id            BIGINT PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    unit_price    DECIMAL(10, 2) NOT NULL,
    stock_qty     INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id            BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL REFERENCES customers(id),
    placed_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Many-to-many between orders and products needs a join table (see topic 4)
CREATE TABLE order_items (
    order_id      BIGINT NOT NULL REFERENCES orders(id),
    product_id    BIGINT NOT NULL REFERENCES products(id),
    quantity      INT NOT NULL CHECK (quantity > 0),
    unit_price    DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Four tables created: customers, products, orders, order_items.
Each customer can have many orders (customer_id foreign key on orders).
Each order can contain many products, and each product can appear on many
orders, resolved through the order_items join table. unit_price is copied
onto order_items so historical orders keep the price at time of purchase
even if products.unit_price changes later.

Exercise

Requirement: "A library lends books to members. We must know which member currently has which book and when it was borrowed." Create three tables, members, books and loans, where a loan links a member to a book with a borrowed_on date. The data is then inserted for you. Answer the question the system must support: the name of the member who has the book titled Dune.

Expected output: name then Asha

-- TODO: members (id, name), books (id, title),
--       loans (id, member_id, book_id, borrowed_on) with foreign keys

INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO books (id, title) VALUES (1, 'Dune'), (2, 'Emma');
INSERT INTO loans (id, member_id, book_id, borrowed_on) VALUES (1, 1, 1, '2026-03-01'), (2, 2, 2, '2026-03-02');

-- TODO: the name of the member who has the book titled 'Dune'

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  borrowed_on DATE NOT NULL
);

INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO books (id, title) VALUES (1, 'Dune'), (2, 'Emma');
INSERT INTO loans (id, member_id, book_id, borrowed_on) VALUES (1, 1, 1, '2026-03-01'), (2, 2, 2, '2026-03-02');

SELECT m.name
FROM members m
JOIN loans l ON l.member_id = m.id
JOIN books b ON b.id = l.book_id
WHERE b.title = 'Dune';

Quick quiz

Interview questions