Blog / CMS Schema

Interactive diagram

Drag to pan, scroll to zoom. Open it in the full editor to edit, rearrange and export.

About this schema

Authors (users) write posts in a category; readers leave comments; posts and tags have a many-to-many relationship through a join table.

Schema (SQL)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE,
  bio TEXT
);
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) UNIQUE
);
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT NOT NULL REFERENCES users(id),
  category_id INT REFERENCES categories(id),
  title VARCHAR(200), slug VARCHAR(220) UNIQUE,
  body TEXT, published_at TIMESTAMP
);
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id),
  author_id INT REFERENCES users(id),
  parent_id INT REFERENCES comments(id),
  body TEXT, created_at TIMESTAMP
);
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(60) UNIQUE NOT NULL
);
CREATE TABLE post_tags (
  post_id INT NOT NULL REFERENCES posts(id),
  tag_id INT NOT NULL REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Open in the editor → to export it as PNG, SVG, Mermaid or DBML.

More examples