Relational Schema Design

Entities and relationships

VaultCommerce's core commerce domain has clear entities:

Cardinality drives table layout: one-to-many relationships get a foreign key on the "many" side. orders.customer_id points to customers.id.

Primary keys and surrogates

Every table needs a stable row identifier. VaultCommerce uses surrogate numeric keys generated by the database:

CREATE TABLE customers (
    id         BIGSERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    full_name  TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status      VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    total_cents INTEGER NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

BIGSERIAL auto-increments; REFERENCES customers(id) enforces that every order belongs to a real customer. Natural keys like email work for lookup but change rarely — surrogate id values never change when a user updates their address.

Junction tables for many-to-many

Products and promotional tags have a many-to-many relationship. Neither side stores an array of IDs — a junction table holds pairs:

CREATE TABLE product_tags (
    product_id BIGINT NOT NULL REFERENCES products(id),
    tag_id     BIGINT NOT NULL REFERENCES tags(id),
    PRIMARY KEY (product_id, tag_id)
);

Composite primary key (product_id, tag_id) prevents duplicate tag assignments.

Modeling money and snapshots

Never store prices as FLOAT. VaultCommerce uses integer cents and snapshots unit price on each order line because catalog prices change:

CREATE TABLE order_items (
    id           BIGSERIAL PRIMARY KEY,
    order_id     BIGINT NOT NULL REFERENCES orders(id),
    product_id   BIGINT NOT NULL REFERENCES products(id),
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL  -- price at purchase time
);

Historical orders stay accurate even when today's products.price_cents differs.

Timestamps and soft deletes

Audit columns appear on every VaultCommerce table:

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Soft deletes use deleted_at TIMESTAMPTZ instead of DELETE — support can restore accounts without losing order history. Application queries add WHERE deleted_at IS NULL.

From diagram to DDL

Design workflow at VaultCommerce: