Project: Blog Database Design

Introduction

This project designs a blog schema—users, posts, comments, categories, tags, and many-to-many links—then writes indexes and sample queries for list pages, detail pages, and tag statistics. You apply normalization, foreign keys, and pagination patterns from earlier chapters.

Prerequisites

Create Schema

Seed Sample Data

Insert at least two users, three posts (one draft), categories, tags, comments, and post_tags links—mirror your own titles.

Query 1: Published List (Pagination)

EXPLAIN — expect idx_posts_user_published or range on published_at after tuning.

Query 2: Post Detail With Author and Comments

App layer often splits into two queries (post + comments) to avoid wide joins.

Query 3: Tag Cloud (Counts)

sql
SELECT
  t.name,
  COUNT(*) AS post_count
FROM tags t
INNER JOIN post_tags pt ON pt.tag_id = t.id
INNER JOIN posts p ON p.id = pt.post_id
WHERE p.published_at IS NOT NULL
GROUP BY t.id, t.name
ORDER BY post_count DESC, t.name ASC
LIMIT 20;

Design Notes

DecisionRationale
slug uniqueSEO-friendly URLs
ON DELETE CASCADE on commentsRemove orphan comments when post deleted
Junction post_tagsMany-to-many normalized
Nullable published_atDraft vs published

Deliverables

  • ER diagram on paper or Workbench
  • All FKs and indexes created
  • Three query types run with EXPLAIN
  • Backup: mysqldump blog_prod

Wire app in Spring Boot or JDBC next.

FAQ

Slug vs id in URL?

Public slug; internal joins use numeric id.

Soft delete posts?

Add deleted_at; filter WHERE deleted_at IS NULL.

Full-text search?

FULLTEXT(title, body) index + MATCH … AGAINST—extension.

Denormalize comment count?

Optional comment_count on posts + trigger/app—trade write complexity.

UUID primary keys?

BINARY(16) or CHAR(36)—distributed ids; BIGINT simpler for learning.

Migrate from blog_dev?

mysqldump selective tables or INSERT SELECT.