Skip to content
Tutorials Center
My learning

Topics

Lesson 67 of 75 ยท Projects

Project: Blog Database

Build a blog database in MySQL: users, posts, many-to-many tags, threaded comments, full-text search, indexes for the front page and reporting queries.

A blog looks simple until you list what it must do. Authors write posts. Posts carry several tags, and each tag belongs to many posts. Readers comment, and comments reply to other comments. The front page needs the newest published posts fast, a tag page needs every post with one tag, and the owner wants numbers: who writes the most, which tags are popular, which posts start conversations.

In this project you design that database step by step, the way you would for a real MySQL application: tables first, then relationships, then the queries the pages will run, then the indexes that make them fast. It pulls together schema design patterns, keys and constraints, joins, recursive CTEs and composite indexes.

What runs live here. The examples use the SQLite stand-in, so the DDL uses portable types (INTEGER, TEXT). The real MySQL version of the schema is shown in a read-only block. Full-text search needs a MySQL FULLTEXT index and is shown, not executed.

What the blog must do

Posts Written by one author, with a status.
Tags Many per post, many posts per tag.
Comments Replies nest under other comments.
Reports Counts per author, tag and day.
1 / 2

Step 1: the entities

Four nouns become four tables, plus one junction table for the many-to-many link:

erDiagram
  USERS ||--o{ POSTS : writes
  POSTS ||--o{ COMMENTS : receives
  COMMENTS ||--o{ COMMENTS : "replies to"
  POSTS ||--o{ POST_TAGS : has
  TAGS ||--o{ POST_TAGS : labels

One user writes many posts, a post has many comments and many tags, and a comment may have a parent comment.

A few decisions are worth stating out loud:

  • Surrogate keys. Every table has an id that never changes. The public identity of a post is its slug (for example join-basics), which is UNIQUE, not the primary key, so you can fix a typo in a URL without rewriting foreign keys.
  • Status as data, with a CHECK. A post is draft, published or archived. A CHECK constraint rejects anything else, and MySQL enforces CHECK since 8.0.16.
  • Soft structure for comments. A comment has a nullable parent_id pointing to another comment in the same table. A NULL means "top level".
  • Composite primary key for the junction table. (post_id, tag_id) stops the same tag being attached twice and doubles as the index for "tags of a post".

Here is the schema with sample data. Run it and look at the post list with its tags joined into one column:

SQL Schema and a post list with tags

Press Run (or Ctrl + Enter) to see the result here.

The five posts come back with their tags in one cell, for example join-basics shows sql, beginner. In MySQL you would write GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', '), which also lets you choose the order, as covered in GROUP BY and aggregates. The LEFT JOIN matters: without it, a post with no tags would vanish from the list.

The same schema in real MySQL

On a MySQL 8.4 server the tables would use sized types, an explicit engine and utf8mb4. This block is read-only:

SQL
CREATE TABLE users (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE posts (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id    BIGINT UNSIGNED NOT NULL,
  title        VARCHAR(200) NOT NULL,
  slug         VARCHAR(160) NOT NULL,
  body         MEDIUMTEXT NOT NULL,
  status       ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  published_at DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_posts_slug (slug),
  KEY ix_posts_status_published (status, published_at),
  CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two things differ from the live version. ENUM replaces the CHECK list (either works; see ENUM and SET in practice for the trade-off), and the foreign key to users gets an automatic index on author_id because InnoDB requires one.

Step 2: threaded comments

Comments reference their parent with parent_id. To show a conversation you need the tree flattened in reading order, with an indentation level. A recursive CTE does that: start from top-level comments, then repeatedly add the replies of the rows found so far. The path column builds a sortable key so replies appear directly under their parent.

SQL Comments as a tree

Press Run (or Ctrl + Enter) to see the result here.

The query returns the five comments of post 1 and leaves out the one that belongs to post 2. They read in thread order: Ana, Mia, Ana (depth 0, 1, 2), then Bo and Mia (depth 0, 1). In MySQL the same query works with LPAD(id, 5, '0') and CONCAT instead of printf and || (the || operator is a logical OR in default MySQL mode). Remember to raise cte_max_recursion_depth if your threads can be deeper than 1,000 levels, which a sane blog never needs.

This design is the adjacency list. It is the simplest to write and to update, but reading a whole thread takes a recursive query. Two alternatives exist: store a materialized path string in the table (fast reads, harder moves), or cap the depth at two levels and avoid recursion completely. Many real sites do the latter, because deeply nested threads are unreadable anyway.

Step 3: the queries each page needs

Write the page queries before choosing indexes, because indexes serve queries, not tables.

  1. Front page: the newest 10 published posts.
  2. Tag page: published posts with one tag, newest first.
  3. Post page: one post by slug, its tags, its comments.
  4. Dashboard: posts and comments per author, popular tags, posts per month.

Queries 1 and 2 are the ones that get millions of hits. Query 3 is a point lookup on the unique slug index, which is already fast. Look at the plan SQLite chooses for the front page without and with an index:

SQL Front page query: before and after an index

Press Run (or Ctrl + Enter) to see the result here.

Before the index the plan is a full scan plus a temporary B-tree used for sorting. After it, the plan searches the index by status and reads it in published_at order, so the LIMIT 10 stops after ten rows and no sort is needed. This is the equality column first, then the sort column rule from designing composite indexes. In MySQL you would confirm the same thing with EXPLAIN and look for the absence of Using filesort (EXPLAIN and EXPLAIN ANALYZE).

The index that serves the tag page is already there: post_tags has primary key (post_id, tag_id), which finds the tags of a post. For the opposite direction, "posts of a tag", add a second index on (tag_id, post_id). Without it, MySQL would scan the whole junction table.

SQL
CREATE INDEX ix_post_tags_tag ON post_tags (tag_id, post_id);

Visitors expect a search box. A LIKE '%word%' query cannot use a B-tree index, so it scans every row. MySQL's answer for plain text is a FULLTEXT index and the MATCH ... AGAINST operator. The next block is read-only (SQLite in the browser has no MySQL full-text engine):

SQL
ALTER TABLE posts ADD FULLTEXT KEY ft_posts_title_body (title, body);

SELECT id, title,
       MATCH (title, body) AGAINST ('composite index' IN NATURAL LANGUAGE MODE) AS score
FROM posts
WHERE status = 'published'
  AND MATCH (title, body) AGAINST ('composite index' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10;

InnoDB full-text search is fine for a modest blog. If you need typo tolerance, facets or millions of documents, move search to a dedicated engine and keep MySQL as the source of truth. The full-text search lesson covers the modes and limits.

Step 5: reporting queries

The owner's dashboard is plain aggregation over the same tables. Run these three reports on a small data set:

SQL Dashboard reports

Press Run (or Ctrl + Enter) to see the result here.

The first report shows Mia with 2 published posts and 5 + 1 = 6 comments, and Omar with 1 post and 2 comments. The subquery is the important idea: if you joined comments directly to posts and then counted posts, every post would be counted once per comment. That "fan-out" mistake is one of the most common reporting bugs, and it is described in joins in MySQL. The tag report ranks sql first with 2 published posts; the monthly report lists 2026-03 with 2 posts and 2026-04 with 1.

In MySQL the monthly grouping would normally be DATE_FORMAT(published_at, '%Y-%m'), and with ONLY_FULL_GROUP_BY active you must group by that same expression or its alias.

Where to take it next

  • Counters. Showing a comment count on every front-page card means a join or subquery per page view. Store comment_count on posts and update it in the same transaction that inserts a comment, or refresh it from a job.
  • Soft deletes. Add deleted_at to comments, and include deleted_at IS NULL in the query and in the matching indexes.
  • Scheduled publishing. A published_at in the future plus published_at <= NOW() in the front-page query needs no extra table.
  • Slug history. When a title changes, keep old slugs in a post_slugs table so old links redirect rather than return 404.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the primary key of post_tags the pair (post_id, tag_id)?

    A composite key makes each pairing unique, and its index finds all tags of a post. A second index on (tag_id, post_id) serves the reverse lookup.
  2. 2. Which index best serves "newest published posts, 10 at a time"?

    The equality column (status) comes first, then the sort column (published_at), so MySQL can read rows already in order and stop after ten.
  3. 3. What does a NULL parent_id mean in the comments table?

    Replies point to another comment through parent_id. Top-level comments have no parent, so their parent_id is NULL.
  4. 4. Why does the author report count comments in a subquery instead of joining comments directly?

    Joining a one-to-many table multiplies the rows of the parent side. Aggregating comments first keeps one row per post.

Summary

  • Model a blog with users, posts, tags, a post_tags junction table and a self-referencing comments table.
  • Use a surrogate id, a unique slug for URLs, and CHECK or ENUM for the status.
  • A recursive CTE flattens an adjacency-list comment tree; a path column gives the reading order.
  • Choose indexes from the page queries: (status, published_at) for the front page, (tag_id, post_id) for tag pages, FULLTEXT for search.
  • Aggregate child tables in a subquery before joining, so counts are not multiplied.
  • Add counters, soft deletes and slug history only when the product needs them.

Next: Project: E-commerce Orders and Inventory.