schema-designlisted

# Schema design The schema outlives the code that reads it. Application code gets rewritten every few years; the data survives, and every wrong decision in it is paid for by every future reader plus a migration. Spend the extra hour here. Examples are Postgres; the principles hold across engines, and engine-specific points are marked. ## Constraints are the cheapest correctness you will ever buy Application code enforcing an invariant protects one code path. A database constraint protects all of them, including the migration script, the manual fix at 3am, and the service someone writes next year. ```sql CREATE TABLE order_item ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id bigint NOT NULL REFERENCES "order"(id) ON DELETE CASCADE, sku text NOT NULL, quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0), created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (order_id, sku) ); ``` Every column above says something the application cannot forget: - **`NOT NULL` by default.** Make nullability the exception you justify. A nullable column forces every reader to answer "what does missing mean here?" — and they will each answer differently. Distinguish "unknown", "not applicable", and "empty"; if two of them exist in one column, you need another column. - **Foreign keys**, with a deliberate `ON DELETE` action. "We enforce it in the app" survives e