Blog / CMS schema template

Tables in this data model

• authors — 4 columns
• categories — 3 columns
• posts — 7 columns
• post_revisions — 4 columns
• tags — 2 columns
• post_tags — 2 columns
• comments — 5 columns
• media — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE authors (
  author_id  INTEGER      NOT NULL PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  bio        TEXT
);

CREATE TABLE categories (
  category_id INTEGER     NOT NULL PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  slug        VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE posts (
  post_id      INTEGER      NOT NULL PRIMARY KEY,
  author_id    INTEGER      NOT NULL REFERENCES authors(author_id),
  category_id  INTEGER      REFERENCES categories(category_id),
  title        VARCHAR(200) NOT NULL,
  slug         VARCHAR(200) NOT NULL UNIQUE,
  status       VARCHAR(20)  NOT NULL,
  published_at TIMESTAMP
);

CREATE TABLE post_revisions (
  revision_id INTEGER   NOT NULL PRIMARY KEY,
  post_id     INTEGER   NOT NULL REFERENCES posts(post_id),
  body        TEXT      NOT NULL,
  saved_at    TIMESTAMP NOT NULL
);

CREATE TABLE tags (
  tag_id INTEGER     NOT NULL PRIMARY KEY,
  name   VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(post_id),
  tag_id  INTEGER NOT NULL REFERENCES tags(tag_id)
);

CREATE TABLE comments (
  comment_id INTEGER      NOT NULL PRIMARY KEY,
  post_id    INTEGER      NOT NULL REFERENCES posts(post_id),
  name       VARCHAR(100),
  body       TEXT         NOT NULL,
  approved   BOOLEAN      NOT NULL
);

CREATE TABLE media (
  media_id    INTEGER      NOT NULL PRIMARY KEY,
  uploaded_by INTEGER      NOT NULL REFERENCES authors(author_id),
  url         VARCHAR(500) NOT NULL,
  alt_text    VARCHAR(200)
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates