April 2026 — 9 min read

Database Schema Examples — MySQL & PostgreSQL Templates

Starting a new database from scratch is easier when you have a concrete example to work from. Below are five common real-world schemas — e-commerce, blog, SaaS user management, task tracker, and messaging — with MySQL and PostgreSQL CREATE TABLE scripts you can copy directly or use as a starting point in SQL Designer.

1. E-Commerce Schema

A minimal but complete schema for an online store: products with categories, customers, orders, and order line items. The order_items table captures the price at time of purchase so that historical orders aren't affected by future price changes.

Key design decisions

price_at_purchase on order_items — stores the price snapshot, 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.
Soft-delete pattern via deleted_at on products — allows retiring a product without breaking order history.

-- 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

A blog platform with authors, posts, categories, tags, and comments. Posts can belong to one category and have many tags via a junction table. Comments support basic threading with a parent_id self-reference.

Key design decisions

published_at allows scheduling posts — NULL means draft.
post_tags is a junction table for the many-to-many relationship between posts and tags.
parent_id on comments enables threaded replies without a separate table.

-- 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

A multi-tenant SaaS schema: organisations with multiple members, subscription plans, and a subscription record linking an organisation to its current plan. Tracks billing dates and status.

Key design decisions

role on memberships — owner, admin, member controls what each user can do within an organisation.
renews_at on subscriptions — used to trigger billing reminders and access revocation.
Separate plans table — changing a plan's price doesn't retroactively alter existing subscriptions because the subscription stores price_per_month at sign-up time.

-- 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

Projects with tasks, assignees, and labels. Tasks can have sub-tasks (same self-referencing pattern as the comment example). Labels are shared across a project.

-- 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

Direct messages and group conversations. A conversation_members junction table connects users to conversations. Messages reference a conversation and a sender.

-- 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()
);

Tips for Adapting These Schemas

Add updated_at columns wherever you'll need to detect changes — use ON UPDATE CURRENT_TIMESTAMP in MySQL or a trigger in PostgreSQL.
Use TIMESTAMPTZ in PostgreSQL instead of TIMESTAMP — it stores UTC and converts correctly per session timezone.
Index your foreign keys — MySQL does this automatically; PostgreSQL does not. Add indexes on any column you'll use in a WHERE or JOIN.
Consider soft deletes carefully — a deleted_at column is convenient but requires filtering in every query. Use it only where you genuinely need an audit trail.
Design visually first — it's easier to spot missing relationships and redundant tables in a diagram than in a text script. Use the SQL Designer demo to drag, connect, and adjust before committing to DDL.

Build these schemas visually

Drag and drop tables, connect relationships, and export a MySQL or PostgreSQL CREATE TABLE script — free, no install, no credit card.

Try the Demo