ErdDocs

Example schemas / Blog Platform

Blog Platform ER diagram

A blog database is three chains: users write posts, readers leave comments that can answer other comments (a self-reference), and tags attach to posts through a pure junction table. It is the classic first schema — every relationship type in five tables.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • users — Both authors and commenters — one identity table, two roles.
  • posts — published_at doubles as the draft flag: NULL means unpublished.
  • comments — Threading via parent_id — a self-reference, same trick as category trees.
  • tags / post_tags — A pure many-to-many: the junction carries no payload, just the pair.

Design decisions worth copying

  • One users table for authors and commenters. Splitting them creates two identities for the same person the moment an author replies to a comment.
  • published_at as nullable timestamp instead of a boolean is_published — the same column answers both "is it live?" and "since when?".
  • post_tags has no id column: the (post_id, tag_id) pair IS the identity. Surrogate keys on pure junctions only add an index nobody uses.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE users (
    id         bigserial    PRIMARY KEY,
    email      varchar(255) NOT NULL UNIQUE,
    name       varchar(80)  NOT NULL,
    joined_at  timestamptz  NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    id           bigserial    PRIMARY KEY,
    author_id    bigint       NOT NULL REFERENCES users(id),
    slug         varchar(160) NOT NULL UNIQUE,
    title        varchar(200) NOT NULL,
    body         text         NOT NULL,
    published_at timestamptz
);
COMMENT ON COLUMN posts.published_at IS 'NULL while the post is a draft';

CREATE TABLE comments (
    id         bigserial   PRIMARY KEY,
    post_id    bigint      NOT NULL REFERENCES posts(id),
    author_id  bigint      NOT NULL REFERENCES users(id),
    parent_id  bigint      REFERENCES comments(id),
    body       text        NOT NULL,
    written_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN comments.parent_id IS 'NULL for top-level; otherwise the comment this answers';

CREATE TABLE tags (
    id    bigserial   PRIMARY KEY,
    name  varchar(40) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id  bigint NOT NULL REFERENCES posts(id),
    tag_id   bigint NOT NULL REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

FAQ

How deep can comment threads nest?

The schema allows any depth — parent_id chains are unbounded. Practical UIs cap rendering depth in the query (a recursive CTE with a level counter), not in the schema.

Why varchar for slug instead of deriving it from the title?

Slugs are permanent URLs; titles get edited. Storing the slug decouples the address from the wording — the alternative breaks every shared link on the first typo fix.

More example schemas