Simple Blog Database Project

Project Overview

A foundational database project for building a blog system. This project covers essential concepts including user management, content creation, comments, and categorization.

Learning Objectives

• Design normalized database schemas
• Implement one-to-many relationships
• Create and use foreign keys
• Work with timestamps and auto-increment
• Practice CRUD operations
• Understand content management systems

Database Schema

Tables

1. users: Blog authors and administrators
2. categories: Content categories
3. posts: Blog articles
4. comments: User comments on posts
5. tags: Article tags
6. post_tags: Many-to-many relationship between posts and tags

Implementation Steps

Step 1: Create Database

CREATE DATABASE blog_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE blog_db;

Step 2: Create Users Table

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    bio TEXT,
    role ENUM('admin', 'author', 'subscriber') DEFAULT 'subscriber',
    avatar_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB;

Step 3: Create Categories Table

CREATE TABLE categories (
    category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    parent_category_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_parent (parent_category_id)
) ENGINE=InnoDB;

Step 4: Create Posts Table

CREATE TABLE posts (
    post_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    excerpt VARCHAR(500),
    content TEXT NOT NULL,
    featured_image VARCHAR(255),
    author_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    view_count INT UNSIGNED DEFAULT 0,
    published_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (author_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_author (author_id),
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    INDEX idx_published_at (published_at),
    FULLTEXT INDEX idx_content_search (title, content)
) ENGINE=InnoDB;

Step 5: Create Comments Table

CREATE TABLE comments (
    comment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED,
    parent_comment_id INT UNSIGNED,
    author_name VARCHAR(100),
    author_email VARCHAR(100),
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending',
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL,
    FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id)
        ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_parent (parent_comment_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

Step 6: Create Tags Table

CREATE TABLE tags (
    tag_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_slug (slug)
) ENGINE=InnoDB;

Step 7: Create Post_Tags Junction Table

CREATE TABLE post_tags (
    post_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
        ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB;

Sample Data

Insert Sample Users

INSERT INTO users (username, email, password_hash, full_name, role) VALUES
    ('admin', 'admin@blog.com', '$2y$10$...', 'Site Administrator', 'admin'),
    ('john_doe', 'john@example.com', '$2y$10$...', 'John Doe', 'author'),
    ('jane_smith', 'jane@example.com', '$2y$10$...', 'Jane Smith', 'author'),
    ('subscriber1', 'sub1@example.com', '$2y$10$...', 'Regular User', 'subscriber');

Insert Sample Categories

INSERT INTO categories (category_name, slug, description) VALUES
    ('Technology', 'technology', 'Tech news and tutorials'),
    ('Lifestyle', 'lifestyle', 'Lifestyle articles'),
    ('Travel', 'travel', 'Travel guides and experiences'),
    ('Food', 'food', 'Recipes and restaurant reviews');

Insert Sample Posts

INSERT INTO posts (title, slug, excerpt, content, author_id, category_id, status, published_at) VALUES
    (
        'Getting Started with MySQL',
        'getting-started-mysql',
        'Learn the basics of MySQL database management',
        'Full article content here...',
        2,
        1,
        'published',
        NOW()
    ),
    (
        '10 Tips for Better Lifestyle',
        '10-tips-better-lifestyle',
        'Improve your daily life with these simple tips',
        'Full article content here...',
        3,
        2,
        'published',
        NOW()
    );

Insert Sample Tags

INSERT INTO tags (tag_name, slug) VALUES
    ('MySQL', 'mysql'),
    ('Database', 'database'),
    ('Tutorial', 'tutorial'),
    ('Beginner', 'beginner');

Link Posts and Tags

INSERT INTO post_tags (post_id, tag_id) VALUES
    (1, 1),  -- Getting Started with MySQL -> MySQL
    (1, 2),  -- Getting Started with MySQL -> Database
    (1, 3),  -- Getting Started with MySQL -> Tutorial
    (1, 4);  -- Getting Started with MySQL -> Beginner

Insert Sample Comments

INSERT INTO comments (post_id, user_id, content, status) VALUES
    (1, 4, 'Great tutorial! Very helpful.', 'approved'),
    (1, 3, 'Thanks for sharing this.', 'approved'),
    (2, 4, 'Interesting tips!', 'approved');

Common Queries

Get All Published Posts with Author and Category

SELECT
    p.post_id,
    p.title,
    p.slug,
    p.excerpt,
    p.view_count,
    p.published_at,
    u.username AS author,
    u.full_name AS author_name,
    c.category_name
FROM posts p
JOIN users u ON p.author_id = u.user_id
LEFT JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10;

Get Single Post with All Details

SELECT
    p.*,
    u.username AS author,
    u.full_name AS author_name,
    u.avatar_url AS author_avatar,
    c.category_name,
    COUNT(DISTINCT com.comment_id) AS comment_count,
    GROUP_CONCAT(DISTINCT t.tag_name) AS tags
FROM posts p
JOIN users u ON p.author_id = u.user_id
LEFT JOIN categories c ON p.category_id = c.category_id
LEFT JOIN comments com ON p.post_id = com.post_id AND com.status = 'approved'
LEFT JOIN post_tags pt ON p.post_id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.tag_id
WHERE p.slug = 'getting-started-mysql'
GROUP BY p.post_id;

Get Comments for Post

SELECT
    c.comment_id,
    c.content,
    c.created_at,
    c.parent_comment_id,
    COALESCE(u.username, c.author_name) AS author,
    u.avatar_url
FROM comments c
LEFT JOIN users u ON c.user_id = u.user_id
WHERE c.post_id = 1 AND c.status = 'approved'
ORDER BY c.created_at ASC;

Get Posts by Category

SELECT
    p.post_id,
    p.title,
    p.slug,
    p.excerpt,
    p.published_at,
    u.username AS author
FROM posts p
JOIN categories c ON p.category_id = c.category_id
JOIN users u ON p.author_id = u.user_id
WHERE c.slug = 'technology' AND p.status = 'published'
ORDER BY p.published_at DESC;

Get Posts by Tag

SELECT
    p.post_id,
    p.title,
    p.slug,
    p.excerpt,
    p.published_at
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.slug = 'mysql' AND p.status = 'published'
ORDER BY p.published_at DESC;

Get Most Popular Posts

SELECT
    p.post_id,
    p.title,
    p.view_count,
    p.published_at,
    u.username AS author,
    COUNT(c.comment_id) AS comment_count
FROM posts p
JOIN users u ON p.author_id = u.user_id
LEFT JOIN comments c ON p.post_id = c.post_id AND c.status = 'approved'
WHERE p.status = 'published'
GROUP BY p.post_id
ORDER BY p.view_count DESC
LIMIT 10;

Search Posts

SELECT
    p.post_id,
    p.title,
    p.excerpt,
    MATCH(p.title, p.content) AGAINST('MySQL tutorial' IN NATURAL LANGUAGE MODE) AS relevance
FROM posts p
WHERE MATCH(p.title, p.content) AGAINST('MySQL tutorial' IN NATURAL LANGUAGE MODE)
  AND p.status = 'published'
ORDER BY relevance DESC;

Get Author Statistics

SELECT
    u.user_id,
    u.username,
    u.full_name,
    COUNT(p.post_id) AS total_posts,
    SUM(p.view_count) AS total_views,
    MAX(p.published_at) AS last_published
FROM users u
LEFT JOIN posts p ON u.user_id = p.author_id AND p.status = 'published'
WHERE u.role = 'author'
GROUP BY u.user_id
ORDER BY total_posts DESC;

Features to Implement

Basic Features

• User registration and authentication
• Create, edit, delete posts
• Publish/unpublish posts
• Comment on posts
• Categorize posts
• Tag posts

Intermediate Features

• Nested comments (replies)
• Draft posts
• Post scheduling
• Comment moderation
• Search functionality
• View counter

Advanced Features

• User roles and permissions
• Featured posts
• Related posts
• RSS feed generation
• Post revisions
• SEO metadata

Practice Exercises

Exercise 1: Basic Queries

1. List all published posts with their authors
2. Count total comments per post
3. Find all posts in a specific category
4. Get most recent 5 posts

Exercise 2: Complex Queries

1. Find posts with no comments
2. Get authors with most posts
3. Find most commented posts
4. Search posts by keyword

Exercise 3: Data Manipulation

1. Update post status from draft to published
2. Delete spam comments
3. Move posts to different category
4. Add tags to existing posts

Exercise 4: Reporting

1. Generate monthly post statistics
2. Calculate engagement rate per post
3. Find inactive authors
4. List popular categories

Extension Ideas

1. Media Library: Add table for managing images/files
2. Post Revisions: Track changes to posts
3. User Following: Users can follow authors
4. Notifications: Notify users of new comments
5. Post Reactions: Like/dislike functionality
6. Reading Time: Calculate estimated reading time
7. Post Series: Link related posts in a series

Next Steps

• E-commerce Schema
• User Authentication System
• Database Design Best Practices

Additional Resources

• MySQL Full-Text Search
• Database Normalization
• WordPress Database Schema (for inspiration)