Database Schema Examples — MySQL & PostgreSQL Templates

1. E-Commerce Schema

A production e-commerce schema typically requires joining at least 5 tables even for a simple product listing query — categories, products, customers, orders, and order line items. The highest-leverage design decision isn't which ORM to use; it's whether you snapshot the price on each order line. You must. Don't look it up from products at query time, or a price change will silently rewrite every historical order total.

Storing price_at_purchase on order_items rather than referencing products.price is the single most important design decision in an e-commerce schema. A price update on the product row should affect future orders only — the historical record must be immutable to keep accounting correct.

Key design decisions

• price_at_purchase on order_items stores the price snapshot at sale time, not a live foreign key to the current product price.
• status as a VARCHAR ENUM-equivalent — use ENUM in MySQL or a CHECK constraint in PostgreSQL for tighter validation at the database level.
• Soft-delete via deleted_at on products lets you retire a product without breaking order history. Every query must filter WHERE deleted_at IS NULL, so use it only where you genuinely need the audit trail.

-- MySQL
CREATE TABLE categories (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    slug       VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL UNIQUE,
    price       DECIMAL(10,2) NOT NULL,
    stock       INT UNSIGNED  NOT NULL DEFAULT 0,
    deleted_at  TIMESTAMP NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE customers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    total       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id          INT UNSIGNED  NOT NULL,
    product_id        INT UNSIGNED  NOT NULL,
    quantity          INT UNSIGNED  NOT NULL,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

2. Blog / CMS Schema

The most common modelling mistake in blog schemas is treating posts-to-tags as one-to-many. It isn't. A post can have many tags, and a tag applies to many posts. That's many-to-many, and it needs a junction table. The schema below adds post_tags with a composite primary key. It also handles threaded comments with a parent_id self-reference, and draft/scheduled posts via a nullable published_at.

A post_tags junction table with PRIMARY KEY (post_id, tag_id) and ON DELETE CASCADE on both foreign keys is the standard SQL pattern for many-to-many post-to-tag relationships. The composite primary key prevents duplicate tag associations, and the index is created automatically alongside it.

Key design decisions

• published_at as a nullable timestamp handles scheduling — NULL means draft. Your application checks WHERE published_at <= NOW() to determine what's live.
• post_tags is the junction table for the many-to-many relationship. The composite PRIMARY KEY (post_id, tag_id) prevents duplicates and doubles as a covering index.
• parent_id on comments enables threaded replies without a separate table. Deep nesting requires a recursive CTE in PostgreSQL or application-side tree logic in MySQL pre-8.0.

-- MySQL
CREATE TABLE authors (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email        VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    bio          TEXT,
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE posts (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id    INT UNSIGNED  NOT NULL,
    category_id  INT UNSIGNED  NULL,
    title        VARCHAR(255)  NOT NULL,
    slug         VARCHAR(255)  NOT NULL UNIQUE,
    body         LONGTEXT      NOT NULL,
    published_at TIMESTAMP     NULL,
    created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id)   REFERENCES authors(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE tags (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)  NOT NULL UNIQUE,
    slug VARCHAR(50)  NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id INT UNSIGNED NOT NULL,
    tag_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

CREATE TABLE comments (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id    INT UNSIGNED NOT NULL,
    parent_id  INT UNSIGNED NULL,
    author     VARCHAR(100) NOT NULL,
    body       TEXT         NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id)   REFERENCES posts(id)    ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE SET NULL
);

3. SaaS User & Subscription Schema

In the 2025 Stack Overflow Developer Survey (65,437 respondents), PostgreSQL reached 55.6% adoption versus MySQL's 40.5% (Stack Overflow, 2025). That shift shows up most clearly in SaaS work. TIMESTAMPTZ, native UUID support, and row-level security map cleanly to multi-tenant requirements. This schema models organisations, members, plans, and subscriptions with the same price-snapshot discipline as the e-commerce example.

According to the Stack Overflow Developer Survey 2025 (n = 65,437), PostgreSQL is used by 55.6% of developers versus MySQL's 40.5% — the first time PostgreSQL has held a clear lead. It ranks first in most-admired (65%) and most-desired (46%) database for the third year running, making it the natural default for new SaaS projects (Stack Overflow, 2025).

Key design decisions

• role on memberships — owner, admin, member controls access within an organisation without a separate permissions table at this scale.
• renews_at on subscriptions drives billing reminders and access revocation at the application layer.
• price_per_month is duplicated on subscriptions for the same reason as price_at_purchase on order_items — changing a plan's price shouldn't alter what existing subscribers are charged.

-- PostgreSQL
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE organisations (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    slug       VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE memberships (
    id              SERIAL PRIMARY KEY,
    user_id         INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    organisation_id INT NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
    role            VARCHAR(20) NOT NULL DEFAULT 'member',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, organisation_id)
);

CREATE TABLE plans (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100)   NOT NULL,
    price_per_month NUMERIC(10, 2) NOT NULL,
    max_members     INT            NOT NULL
);

CREATE TABLE subscriptions (
    id              SERIAL PRIMARY KEY,
    organisation_id INT            NOT NULL REFERENCES organisations(id),
    plan_id         INT            NOT NULL REFERENCES plans(id),
    price_per_month NUMERIC(10, 2) NOT NULL,
    status          VARCHAR(20)    NOT NULL DEFAULT 'active',
    renews_at       TIMESTAMPTZ    NOT NULL,
    created_at      TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

4. Task Tracker Schema

Task schemas look deceptively simple until sub-tasks, assignees, and labels enter the picture. The parent_id self-reference on tasks handles sub-tasks in a single table. Labels are scoped to a project via a foreign key, then linked to individual tasks through a junction table. The cascade rules here matter: deleting a project removes its tasks; removing a label removes the association, not the task itself.

The self-referencing parent_id pattern handles subtask nesting within a single tasks table. Setting ON DELETE SET NULL on the parent_id foreign key preserves orphaned sub-tasks as independent items when a parent is deleted, rather than silently cascading the delete down the tree.

-- MySQL
CREATE TABLE projects (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(255) NOT NULL
);

CREATE TABLE tasks (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id  INT UNSIGNED NOT NULL,
    parent_id   INT UNSIGNED NULL,
    assigned_to INT UNSIGNED NULL,
    title       VARCHAR(255) NOT NULL,
    description TEXT,
    status      VARCHAR(20)  NOT NULL DEFAULT 'todo',
    due_date    DATE         NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id)  REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id)   REFERENCES tasks(id)    ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id)    ON DELETE SET NULL
);

CREATE TABLE labels (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    name       VARCHAR(50)  NOT NULL,
    color      CHAR(7)      NOT NULL DEFAULT '#cccccc',
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE task_labels (
    task_id  INT UNSIGNED NOT NULL,
    label_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (task_id, label_id),
    FOREIGN KEY (task_id)  REFERENCES tasks(id)  ON DELETE CASCADE,
    FOREIGN KEY (label_id) REFERENCES labels(id) ON DELETE CASCADE
);

5. Messaging / Chat Schema

The key structural choice in a messaging schema is whether direct messages and group conversations share a table. A unified conversations table with an is_group flag means one messages table serves both cases — fewer joins, simpler queries. The conversation_members junction table connects users to conversations and works identically for 1-on-1 and group chats.

A unified conversations table with is_group BOOLEAN handles both direct messages and group chats through the same foreign key path. The conversation_members junction table enforces that a user must be a member of a conversation before they can read or write to it — a constraint you can push down to row-level security in PostgreSQL rather than enforce entirely in application code.

-- PostgreSQL
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    username   VARCHAR(50)  NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE conversations (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(255) NULL,  -- NULL for direct messages
    is_group   BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE conversation_members (
    conversation_id INT         NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    user_id         INT         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    joined_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE messages (
    id              SERIAL PRIMARY KEY,
    conversation_id INT         NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    sender_id       INT         NOT NULL REFERENCES users(id),
    body            TEXT        NOT NULL,
    sent_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);