MySQL Blog Database Project: Posts, Comments, Tags

Project goal: the database behind a blog like the one you are reading. You will use almost everything from the earlier chapters: keys, relationships, joins, aggregates, indexes and full-text search.

Requirements

Authors write posts. A post has a title, a unique URL slug, a body, a status (draft or published) and a publication date.
A post belongs to one category (optional) and can have many tags.
Visitors write comments on published posts. When a post is deleted, its comments go with it.
The site needs: the latest published posts with author and comment count, a page per tag, a monthly archive, and a search.

Tables and relationships

Table	Holds	Relationship
`users`	Authors	One user writes many posts
`categories`	Topics	One category has many posts
`posts`	Articles	Belongs to a user and (optionally) a category
`comments`	Visitor comments	Many comments belong to one post (cascade delete)
`tags`	Labels	Many-to-many with posts
`post_tags`	The junction table	One row per (post, tag)

The schema

query.sql

SQL

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email)
);

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    slug VARCHAR(40) NOT NULL,
    UNIQUE KEY uq_categories_slug (slug)
);

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL,
    body TEXT NOT NULL,
    status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    published_at DATETIME NULL,
    UNIQUE KEY uq_posts_slug (slug),
    KEY idx_posts_status_date (status, published_at),
    FULLTEXT KEY ft_posts_title_body (title, body),
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT fk_posts_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL,
    CONSTRAINT chk_published_has_date CHECK (status = 'draft' OR published_at IS NOT NULL)
);

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    author_name VARCHAR(40) NOT NULL,
    body VARCHAR(500) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_comments_post (post_id, created_at),
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE
);

CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    UNIQUE KEY uq_tags_name (name)
);

CREATE TABLE post_tags (
    post_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    KEY idx_post_tags_tag (tag_id),
    CONSTRAINT fk_post_tags_post FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    CONSTRAINT fk_post_tags_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Design notes: the slug is UNIQUE (URLs must not clash). ON DELETE SET NULL keeps posts when a category is removed. The CHECK makes sure a published post always has a date. The index (status, published_at) serves “latest published posts”. ft_posts_title_body powers the search.

Sample data

query.sql

SQL

INSERT INTO users (username, email) VALUES ('asha', 'asha@example.com'), ('ben', 'ben@example.com'), ('cara', 'cara@example.com');
INSERT INTO categories (name, slug) VALUES ('Databases', 'databases'), ('PHP', 'php'), ('Career', 'career');
INSERT INTO tags (name) VALUES ('mysql'), ('performance'), ('beginner'), ('security'), ('php');

INSERT INTO posts (user_id, category_id, title, slug, body, status, published_at) VALUES
(1, 1, 'MySQL Indexes Explained', 'mysql-indexes-explained', 'An index lets MySQL find rows without scanning the whole table. Learn how B-tree indexes work.', 'published', '2026-01-10 09:00:00'),
(1, 1, 'Slow Queries and EXPLAIN', 'slow-queries-explain', 'Use EXPLAIN to see how MySQL runs a query and find missing indexes.', 'published', '2026-02-05 10:30:00'),
(2, 2, 'Prepared Statements in PHP', 'prepared-statements-php', 'Prepared statements stop SQL injection. Bind values instead of building strings.', 'published', '2026-02-20 14:00:00'),
(2, 3, 'Your First Developer Job', 'first-developer-job', 'Tips for the first year as a developer.', 'published', '2026-03-02 08:15:00'),
(1, 1, 'Draft: Window Functions', 'draft-window-functions', 'Work in progress about window functions.', 'draft', NULL);

INSERT INTO post_tags (post_id, tag_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (3, 4), (3, 5), (4, 3), (5, 1);

INSERT INTO comments (post_id, author_name, body, created_at) VALUES
(1, 'Rohan', 'Great explanation!', '2026-01-11 10:00:00'),
(1, 'Isha', 'What about composite indexes?', '2026-01-12 18:20:00'),
(2, 'Kabir', 'EXPLAIN saved me today.', '2026-02-06 09:45:00'),
(3, 'Zoya', 'Thanks, I was using string concatenation.', '2026-02-21 11:10:00');

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 9 rows affected
Records: 9  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query 1: the home page

Latest published posts with author, category and comment count:

query.sql

SQL

SELECT p.id, p.title, u.username AS author, c.name AS category, COUNT(cm.id) AS comments, p.published_at
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN categories c ON c.id = p.category_id
LEFT JOIN comments cm ON cm.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.username, c.name, p.published_at
ORDER BY p.published_at DESC
LIMIT 3;

Output

OUTPUT

+----+----------------------------+--------+-----------+----------+---------------------+
| id | title                      | author | category  | comments | published_at        |
+----+----------------------------+--------+-----------+----------+---------------------+
|  4 | Your First Developer Job   | ben    | Career    |        0 | 2026-03-02 08:15:00 |
|  3 | Prepared Statements in PHP | ben    | PHP       |        1 | 2026-02-20 14:00:00 |
|  2 | Slow Queries and EXPLAIN   | asha   | Databases |        1 | 2026-02-05 10:30:00 |
+----+----------------------------+--------+-----------+----------+---------------------+
3 rows in set

LEFT JOIN comments keeps posts with zero comments (the count is 0). With an inner join, they would vanish.

Query 2: the page for a tag

query.sql

SQL

SELECT p.title, p.published_at
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name = 'mysql' AND p.status = 'published'
ORDER BY p.published_at DESC;

Output

OUTPUT

+--------------------------+---------------------+
| title                    | published_at        |
+--------------------------+---------------------+
| Slow Queries and EXPLAIN | 2026-02-05 10:30:00 |
| MySQL Indexes Explained  | 2026-01-10 09:00:00 |
+--------------------------+---------------------+
2 rows in set

query.sql

SQL

SELECT t.name, COUNT(*) AS posts
FROM tags t
JOIN post_tags pt ON pt.tag_id = t.id
JOIN posts p ON p.id = pt.post_id AND p.status = 'published'
GROUP BY t.id, t.name
ORDER BY posts DESC, t.name;

Output

OUTPUT

+-------------+-------+
| name        | posts |
+-------------+-------+
| beginner    |     2 |
| mysql       |     2 |
| performance |     2 |
| php         |     1 |
| security    |     1 |
+-------------+-------+
5 rows in set

The draft post’s mysql tag is not counted, because the join to published posts excludes it.

Query 4: authors and their post counts

query.sql

SQL

SELECT u.username, COUNT(p.id) AS published_posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'published'
GROUP BY u.id, u.username
ORDER BY published_posts DESC, u.username;

Output

OUTPUT

+----------+-----------------+
| username | published_posts |
+----------+-----------------+
| asha     |               2 |
| ben      |               2 |
| cara     |               0 |
+----------+-----------------+
3 rows in set

Cara has written nothing, and still appears with 0. The status condition is in the ON clause, not in WHERE: a WHERE condition on p.status would remove the rows with no post and turn the LEFT JOIN into an inner join (LEFT JOIN).

Query 5: the monthly archive

query.sql

SQL

SELECT DATE_FORMAT(published_at, '%Y-%m') AS month, COUNT(*) AS posts
FROM posts
WHERE status = 'published'
GROUP BY DATE_FORMAT(published_at, '%Y-%m')
ORDER BY month DESC;

Output

OUTPUT

+---------+-------+
| month   | posts |
+---------+-------+
| 2026-03 |     1 |
| 2026-02 |     2 |
| 2026-01 |     1 |
+---------+-------+
3 rows in set

Query 6: search

query.sql

SQL

SELECT id, title
FROM posts
WHERE status = 'published' AND MATCH(title, body) AGAINST('+index* +mysql' IN BOOLEAN MODE)
ORDER BY id;

Output

OUTPUT

+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | MySQL Indexes Explained  |
|  2 | Slow Queries and EXPLAIN |
+----+--------------------------+
2 rows in set

MATCH ... AGAINST uses the FULLTEXT index. In boolean mode, +word means the word is required, and * is a prefix match. This finds the two posts about indexes, and it is much faster than LIKE '%index%', which cannot use an index (FULLTEXT).

Rules in action

query.sql

SQL

INSERT INTO posts (user_id, title, slug, body, status) VALUES (1, 'No date', 'no-date', 'x', 'published');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_published_has_date' is violated.

The CHECK constraint refuses a published post without a date. Deleting a post removes its comments and tag links by itself:

query.sql

SQL

1
2

DELETE FROM posts WHERE id = 2;
SELECT (SELECT COUNT(*) FROM comments WHERE post_id = 2) AS comments_left, (SELECT COUNT(*) FROM post_tags WHERE post_id = 2) AS tag_links_left;

Output

OUTPUT

+---------------+----------------+
| comments_left | tag_links_left |
+---------------+----------------+
|             0 |              0 |
+---------------+----------------+
1 row in set

Common mistakes

Tags in a comma-separated column. You cannot index it or join on it. Use post_tags (Schema Anti-Patterns).
Counting after two joins. Joining comments and tags to a post, then COUNT(*), multiplies the rows (3 comments x 3 tags = 9). Aggregate each side separately (a subquery or CTE).
A WHERE on the right table of a LEFT JOIN, which silently turns it into an inner join.
LIKE '%word%' for search on big tables: a full scan every time.
No unique slug. Two posts with the same URL.
Deleting a user with posts with no plan. Here fk_posts_user has no CASCADE, so MySQL refuses. Decide whether to reassign, archive or block.

Final result

A quick check that all the pieces exist and fit together:

query.sql

SQL

SELECT 'users' AS table_name, COUNT(*) AS row_count FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'posts', COUNT(*) FROM posts
UNION ALL SELECT 'comments', COUNT(*) FROM comments
UNION ALL SELECT 'tags', COUNT(*) FROM tags
UNION ALL SELECT 'post_tags', COUNT(*) FROM post_tags;

Output

OUTPUT

+------------+-----------+
| table_name | row_count |
+------------+-----------+
| users      |         3 |
| categories |         3 |
| posts      |         4 |
| comments   |         3 |
| tags       |         5 |
| post_tags  |         7 |
+------------+-----------+
6 rows in set

The counts reflect the earlier delete of post 2 (4 posts, 3 comments, 7 tag links). Six tables, every relationship enforced by a foreign key, the rules protected by constraints, and the main pages answered by one query each. Next steps: add a comments.parent_id for replies (Self-Referencing Tables), a slug history for redirects (Price History uses the same pattern), and a read-only account for the web application (Application Users).

Requirements

Tables and relationships

The schema

Sample data

Query 1: the home page

Query 2: the page for a tag

Query 3: a tag cloud

Query 4: authors and their post counts

Query 5: the monthly archive

Query 6: search

Rules in action

Common mistakes

Final result