Crowsfoot

Blog database schema: ER diagram and SQL

A blog needs surprisingly few tables: people, the things they write, the replies those draw, and a way to label posts by topic. This schema also works as a starting point for a news site or a simple CMS.

Open this schema in the editor Jump to the SQL
users PK id bigint username varchar email varchar created_at timestamp posts PK id bigint FK author_id bigint title varchar body text published_at timestamp comments PK id bigint FK post_id bigint FK user_id bigint body text created_at timestamp tags PK id bigint name varchar post_tags PK post_id bigint PK tag_id bigint

Tables in this schema

users

Authors and commenters.

posts

Articles, each written by one user.

comments

Replies on a post, each written by a user.

tags

Labels for topics, such as “databases” or “travel”.

post_tags

A junction table connecting posts and tags. A post can have many tags and a tag can label many posts.

How the tables relate

  • posts.author_id references users.id: one row in users can have many rows in posts.
  • comments.post_id references posts.id: one row in posts can have many rows in comments.
  • comments.user_id references users.id: one row in users can have many rows in comments.
  • post_tags is a junction table between posts and tags: many-to-many.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE users (
  id bigint PRIMARY KEY,
  username varchar(40) NOT NULL,
  email varchar(255) NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE posts (
  id bigint PRIMARY KEY,
  author_id bigint NOT NULL REFERENCES users (id),
  title varchar(200) NOT NULL,
  body text NOT NULL,
  published_at timestamptz
);

CREATE TABLE comments (
  id bigint PRIMARY KEY,
  post_id bigint NOT NULL REFERENCES posts (id),
  user_id bigint NOT NULL REFERENCES users (id),
  body text NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE tags (
  id bigint PRIMARY KEY,
  name varchar(50) NOT NULL
);

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

Design notes

  • Comments reference both the post and the user, so you can list a post’s comments or a user’s activity.
  • post_tags uses a composite key so a tag can’t be attached twice to the same post.
  • Leaving published_at empty is a simple way to support drafts.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.