Blogging Platform Database – SQL Schema and Design

Overview

A Blogging Platform is a web-based system that allows users to publish articles, manage categories and tags, and interact via comments. Designing this system in SQL teaches learners how to manage content-heavy applications with user-generated data, relationships, and content metadata.

Key Features of a Blogging Platform

Manage users (authors, readers)
Create, update, and delete blog posts
Categorize posts with tags and categories
Allow users to comment on posts
Track publish dates, updates, and status

Database Design – Tables and Relationships

1. Users Table

Stores user account information.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    full_name VARCHAR(100),
    role ENUM('admin', 'author', 'reader') DEFAULT 'reader',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. Posts Table

Stores blog post content and metadata.

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200),
    content TEXT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

3. Categories Table

Optional grouping for blog posts.

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) UNIQUE
);

4. Post_Categories Table

Many-to-many relation between posts and categories.

CREATE TABLE post_categories (
    post_id INT,
    category_id INT,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

5. Tags Table

Stores individual tags.

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(50) UNIQUE
);

6. Post_Tags Table

Many-to-many relationship for tagging posts.

CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

7. Comments Table

Stores user comments on blog posts.

CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Sample Queries

Add a New Blog Post

INSERT INTO posts (user_id, title, content, status)
VALUES (1, 'How to Learn SQL Fast', 'Start with basic SELECT queries...', 'published');

Get All Published Posts by a Specific Author

SELECT title, created_at
FROM posts
WHERE user_id = 1 AND status = 'published';

Add a Comment to a Post

INSERT INTO comments (post_id, user_id, content)
VALUES (10, 2, 'Great article! Very helpful.');

Find All Posts Tagged with "SQL"

SELECT p.title
FROM posts p
JOIN post_tags pt ON p.post_id = pt.post_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE t.tag_name = 'SQL';

Best Practices

Use ENUM for status fields (e.g., post status, user roles)
Normalize the schema with many-to-many tables for tags and categories
Add indexes on foreign keys and searchable columns (e.g., title, tag_name)
Use triggers or application logic to maintain tag counts or post slugs
Store passwords securely using hashed values only