0Pricing
SQL Academy · Lesson

Modelling a Blog: Users Posts Comments

Draw an ER diagram, identify entities and relationships, and translate them into a normalised SQL schema.

Modelling a Blog: Users Posts Comments is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Domain

A simple blog: users write posts, other users comment on posts. Let's turn that into a schema.

Entities

Three core nouns:

  • User — id, email, name
  • Post — id, author, title, body, published timestamp
  • Comment — id, author, post it's on, body

Relationships

Cardinalities:

  • User 1 ── ∞ Post (a user writes many posts)
  • User 1 ── ∞ Comment
  • Post 1 ── ∞ Comment

User Table

Start with users — others reference it:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Post Table

FK to users:

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  title VARCHAR(200) NOT NULL,
  body  TEXT NOT NULL,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Comment Table

Two FKs:

CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Why ON DELETE CASCADE on Comments?

If a post is deleted, its comments are meaningless — let the DB clean them up automatically.

Why ON DELETE RESTRICT on Author?

Don't silently delete content when a user disappears. Force the app to soft-delete the user or reassign the content first.

Extending to Tags (Many-to-Many)

Add tags via a join table:

CREATE TABLE tags (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Threaded Comments

Self-referencing comments for replies:

ALTER TABLE comments
  ADD COLUMN parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE;

Soft Delete vs Hard Delete

For a blog, soft-deleting posts is often kinder — broken links don't fail in user history. Add deleted_at TIMESTAMPTZ and filter WHERE deleted_at IS NULL in every query.

ER Diagram in Words

users (1) -< (∞) posts (1) -< (∞) comments

Plus: users (1) -< (∞) comments, posts (∞) >-< (∞) tags.

Recap

Designing a schema starts with entities, relationships and cardinalities.

  • Surrogate PK on every table
  • FKs declare the relationships
  • Cascade vs Restrict matches business intent
  • Many-to-many = join table

Quick Check

Where does the foreign key live in a one-to-many relationship between posts and comments?

Frequently asked questions

Is the “Modelling a Blog: Users Posts Comments” lesson free?

Yes — the full text of “Modelling a Blog: Users Posts Comments” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Modelling a Blog: Users Posts Comments”?

Draw an ER diagram, identify entities and relationships, and translate them into a normalised SQL schema. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Modelling a Blog: Users Posts Comments” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Modelling a Blog: Users Posts Comments
  2. Choosing Keys and Types
  3. Indexes for Common Queries
  4. Seeding the Database with Test Data
← Back to SQL Academy