Database Design

A schema is a set of decisions about what the business needs to record. Design is the work of getting from a description in words to those decisions, in an order that does not leave you rewriting the first half.

The goal is worth stating precisely, because it is not "a correct schema". Several correct schemas exist for any requirement. The goal is a schema that makes the queries the application actually runs straightforward to write, and makes the data the business considers invalid impossible to store.

This lesson works through one new requirement against the schema you already have, so the design decisions are concrete rather than abstract.

The requirement for this lesson: customers may return items they have ordered. A return refers to a specific line on a specific order, and may cover some or all of the quantity on that line. Every return records a reason from a fixed list — damaged, wrong item, no longer needed, faulty — and the list is maintained by the business. A return is raised, then approved or rejected by an employee, and an approved return may produce a refund. Returns must be reportable by product, by reason and by month.

Working through the steps: the nouns with facts of their own are return and reason. The employee, order line and product already exist. A reason has a code and a description and the business maintains the list, which makes it a lookup table rather than free text. A return refers to one order line, and an order line can have several returns, because partial returns are allowed. The status is a small fixed set with no facts of its own, so it is a column with a constraint rather than a table.

-- Business-maintained list. Surrogate key, natural code kept unique.
CREATE TABLE return_reasons (
    reason_id   INT IDENTITY(1,1) NOT NULL,
    reason_code NVARCHAR(30)      NOT NULL,
    description NVARCHAR(200)     NOT NULL,
    is_active   BIT               NOT NULL CONSTRAINT df_return_reasons_active DEFAULT (1),
    CONSTRAINT pk_return_reasons PRIMARY KEY (reason_id),
    CONSTRAINT uq_return_reasons_code UNIQUE (reason_code)
);

CREATE TABLE returns (
    return_id        INT IDENTITY(1,1) NOT NULL,
    order_item_id    INT               NOT NULL,
    reason_id        INT               NOT NULL,
    quantity         INT               NOT NULL,
    status           NVARCHAR(20)      NOT NULL
        CONSTRAINT df_returns_status DEFAULT ('raised'),
    raised_on        DATETIME2         NOT NULL
        CONSTRAINT df_returns_raised DEFAULT (SYSUTCDATETIME()),
    decided_on       DATETIME2         NULL,
    decided_by       INT               NULL,
    refund_amount    DECIMAL(10,2)     NULL,

    CONSTRAINT pk_returns PRIMARY KEY (return_id),

    CONSTRAINT fk_returns_order_item FOREIGN KEY (order_item_id)
        REFERENCES order_items (order_item_id),
    CONSTRAINT fk_returns_reason FOREIGN KEY (reason_id)
        REFERENCES return_reasons (reason_id),
    CONSTRAINT fk_returns_employee FOREIGN KEY (decided_by)
        REFERENCES employees (employee_id),

    -- "may cover some or all of the quantity" — never zero, never negative
    CONSTRAINT ck_returns_quantity CHECK (quantity > 0),

    -- "raised, then approved or rejected" — the only permitted values
    CONSTRAINT ck_returns_status CHECK (status IN ('raised', 'approved', 'rejected')),

    -- A decision needs both a decider and a date, or neither
    CONSTRAINT ck_returns_decision CHECK (
        (status = 'raised'  AND decided_on IS NULL AND decided_by IS NULL)
     OR (status <> 'raised' AND decided_on IS NOT NULL AND decided_by IS NOT NULL)
    ),

    -- Only an approved return carries a refund, and it cannot be negative
    CONSTRAINT ck_returns_refund CHECK (
        (status = 'approved' AND (refund_amount IS NULL OR refund_amount >= 0))
     OR (status <> 'approved' AND refund_amount IS NULL)
    )
);

CREATE INDEX ix_returns_order_item ON returns (order_item_id);
CREATE INDEX ix_returns_reason_raised ON returns (reason_id, raised_on);

• Look at which columns are nullable and which are not. NOT NULL on order_item_id, reason_id and quantity says a return without them is not a return. decided_by and refund_amount are nullable because they are genuinely unknown while the return is still being considered. Nullability is a design statement, not a default to accept.
• ck_returns_quantity comes straight from "some or all of the quantity". Without it the table accepts a return for zero items, or minus three, and every report that sums quantities has to defend against values the schema should have refused.
• ck_returns_status replaces a comment about permitted values with a rule the database enforces. A typo of 'aproved' is rejected at the moment of the bad write, rather than becoming a row that no report counts and nobody notices. The alternative design is a statuses lookup table with a foreign key, which is better when the list grows or needs display names.
• ck_returns_decision is the constraint most designs omit. It enforces a relationship between columns — a decided return has a decider and a date, an undecided one has neither — which prevents the half-populated rows that make later reporting unreliable.
• The two indexes follow from the questions in step two. Reporting by reason and month uses (reason_id, raised_on); finding the returns against a line uses order_item_id. Each index also costs something on every write, so they are added for named queries rather than on every column that looks useful.
• One rule is deliberately not here: that the returned quantity cannot exceed the quantity ordered on that line. A CHECK constraint can only see columns in its own row, so this needs a trigger or an application-level check inside a transaction. Recognising which rules a CHECK cannot express is part of the design, and quietly leaving such a rule out is how it ends up enforced nowhere.