MySQL & Database Design

Blog & CMS Database Design

13 min Lesson 37 of 40

Blog & CMS Database Design

A blog and Content Management System (CMS) requires a flexible database design to handle various content types, taxonomies, multi-language support, and user permissions. In this lesson, we'll create a complete CMS database that can power blogs, news sites, and content-heavy applications.

CMS Core Requirements

A robust CMS needs to support:

Content Management:
- Posts with multiple content types
- Categories and hierarchical taxonomies
- Tags and custom taxonomies
- Featured images and media galleries
- Drafts, published, and scheduled posts

Multi-Language Support:
- Content in multiple languages
- Language-specific URLs
- Translation management

User Roles & Permissions:
- Authors, editors, administrators
- Content workflow (draft → review → publish)
- Permission-based access control

Advanced Features:
- Post revisions and version history
- Comments and moderation
- SEO metadata
- Custom fields/meta data

Complete CMS Schema

Let's build the database structure:

-- Users and roles
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    role ENUM('subscriber', 'author', 'editor', 'admin') DEFAULT 'subscriber',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (role),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Languages
CREATE TABLE languages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL COMMENT 'e.g., en, ar, fr',
    name VARCHAR(50) NOT NULL,
    native_name VARCHAR(50) NOT NULL,
    is_rtl BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    INDEX idx_code (code)
) ENGINE=InnoDB;

-- Categories (hierarchical)
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    slug VARCHAR(200) NOT NULL,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_parent (parent_id),
    INDEX idx_slug (slug)
) ENGINE=InnoDB;

-- Category translations
CREATE TABLE category_translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    meta_title VARCHAR(255),
    meta_description VARCHAR(500),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_translation (category_id, language_id),
    INDEX idx_category (category_id),
    INDEX idx_language (language_id)
) ENGINE=InnoDB;

-- Tags
CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(200) UNIQUE NOT NULL,
    post_count INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_popular (post_count)
) ENGINE=InnoDB;

-- Tag translations
CREATE TABLE tag_translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_id INT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_translation (tag_id, language_id),
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB;

-- Posts
CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id BIGINT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    slug VARCHAR(255) NOT NULL,
    post_type ENUM('post', 'page', 'custom') DEFAULT 'post',
    status ENUM('draft', 'pending', 'published', 'scheduled', 'archived') DEFAULT 'draft',
    featured_image_url VARCHAR(500),
    comment_status ENUM('open', 'closed', 'registered_only') DEFAULT 'open',
    view_count INT UNSIGNED DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_author (author_id),
    INDEX idx_category (category_id),
    INDEX idx_slug (slug),
    INDEX idx_status_published (status, published_at),
    INDEX idx_featured (is_featured, published_at)
) ENGINE=InnoDB;

-- Post translations (multi-language content)
CREATE TABLE post_translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    excerpt TEXT,
    content LONGTEXT NOT NULL,
    meta_title VARCHAR(255),
    meta_description VARCHAR(500),
    meta_keywords VARCHAR(500),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_translation (post_id, language_id),
    INDEX idx_post (post_id),
    INDEX idx_language (language_id),
    FULLTEXT idx_search (title, content)
) ENGINE=InnoDB;

-- Post tags (many-to-many)
CREATE TABLE post_tags (
    post_id BIGINT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB;

-- Post revisions (version history)
CREATE TABLE post_revisions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    revision_note VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_post (post_id, created_at),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Media library
CREATE TABLE media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(50) NOT NULL COMMENT 'MIME type',
    file_size INT UNSIGNED NOT NULL COMMENT 'Size in bytes',
    width INT UNSIGNED COMMENT 'For images',
    height INT UNSIGNED COMMENT 'For images',
    alt_text VARCHAR(255),
    caption TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_user (user_id),
    INDEX idx_type (file_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Post media (attachment relationship)
CREATE TABLE post_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    media_id BIGINT UNSIGNED NOT NULL,
    display_order INT DEFAULT 0,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_media (media_id)
) ENGINE=InnoDB;

-- Comments
CREATE TABLE comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL COMMENT 'NULL for guest comments',
    parent_comment_id BIGINT UNSIGNED NULL,
    author_name VARCHAR(100) NOT NULL,
    author_email VARCHAR(255) NOT NULL,
    author_url VARCHAR(255),
    author_ip VARCHAR(45),
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    INDEX idx_post_status (post_id, status),
    INDEX idx_user (user_id),
    INDEX idx_parent (parent_comment_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Post meta (custom fields)
CREATE TABLE post_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    INDEX idx_post_key (post_id, meta_key),
    INDEX idx_key (meta_key)
) ENGINE=InnoDB;

-- Menus
CREATE TABLE menus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(50) NOT NULL COMMENT 'e.g., header, footer, sidebar',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_location (location)
) ENGINE=InnoDB;

-- Menu items
CREATE TABLE menu_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id INT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    title VARCHAR(100) NOT NULL,
    url VARCHAR(500),
    target VARCHAR(20) DEFAULT '_self',
    css_class VARCHAR(100),
    display_order INT DEFAULT 0,
    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    INDEX idx_menu (menu_id),
    INDEX idx_parent (parent_id),
    INDEX idx_order (display_order)
) ENGINE=InnoDB;

-- Redirects (for SEO)
CREATE TABLE redirects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    old_url VARCHAR(500) NOT NULL,
    new_url VARCHAR(500) NOT NULL,
    redirect_type ENUM('301', '302', '307') DEFAULT '301',
    hit_count INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_old_url (old_url),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- SEO settings per post
CREATE TABLE seo_data (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    focus_keyword VARCHAR(255),
    canonical_url VARCHAR(500),
    noindex BOOLEAN DEFAULT FALSE,
    nofollow BOOLEAN DEFAULT FALSE,
    schema_type VARCHAR(50) COMMENT 'e.g., Article, NewsArticle, BlogPosting',
    schema_data JSON,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_seo (post_id, language_id)
) ENGINE=InnoDB;

Multi-Language Strategy: We use separate translation tables for content, allowing each language to have its own title, content, and SEO metadata while sharing core post data.

Essential CMS Queries

Key queries for CMS functionality:

-- Get published posts with translations
SELECT
    p.id,
    p.slug,
    p.published_at,
    p.view_count,
    pt.title,
    pt.excerpt,
    u.display_name AS author,
    c.slug AS category_slug,
    ct.name AS category_name
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN category_translations ct ON c.id = ct.category_id
    AND ct.language_id = pt.language_id
WHERE p.status = 'published'
    AND pt.language_id = 1
    AND p.published_at <= NOW()
ORDER BY p.published_at DESC
LIMIT 10;

-- Get post with all details (single post view)
SELECT
    p.*,
    pt.title,
    pt.content,
    pt.meta_title,
    pt.meta_description,
    u.display_name AS author,
    u.avatar_url AS author_avatar,
    c.slug AS category_slug,
    ct.name AS category_name
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN category_translations ct ON c.id = ct.category_id
    AND ct.language_id = pt.language_id
WHERE p.slug = 'my-post-slug'
    AND pt.language_id = 1
    AND p.status = 'published';

-- Get post tags
SELECT
    t.id,
    t.slug,
    tt.name
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
JOIN tag_translations tt ON t.id = tt.tag_id
WHERE pt.post_id = 100
    AND tt.language_id = 1;

-- Get approved comments with nested replies
WITH RECURSIVE comment_tree AS (
    SELECT
        c.id,
        c.post_id,
        c.parent_comment_id,
        c.author_name,
        c.content,
        c.created_at,
        0 AS depth,
        CAST(c.id AS CHAR(500)) AS path
    FROM comments c
    WHERE c.post_id = 100
        AND c.status = 'approved'
        AND c.parent_comment_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.post_id,
        c.parent_comment_id,
        c.author_name,
        c.content,
        c.created_at,
        ct.depth + 1,
        CONCAT(ct.path, '->', c.id)
    FROM comments c
    JOIN comment_tree ct ON c.parent_comment_id = ct.id
    WHERE c.status = 'approved'
        AND ct.depth < 5
)
SELECT * FROM comment_tree
ORDER BY path;

-- Search posts across all languages
SELECT
    p.id,
    p.slug,
    pt.title,
    pt.excerpt,
    l.code AS language,
    MATCH(pt.title, pt.content) AGAINST('search term') AS relevance
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
JOIN languages l ON pt.language_id = l.id
WHERE p.status = 'published'
    AND MATCH(pt.title, pt.content) AGAINST('search term' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 20;

-- Get posts by category with subcategories
WITH RECURSIVE category_tree AS (
    SELECT id FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    p.id,
    p.slug,
    pt.title,
    p.published_at
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
WHERE p.category_id IN (SELECT id FROM category_tree)
    AND p.status = 'published'
    AND pt.language_id = 1
ORDER BY p.published_at DESC;

-- Get popular posts by views
SELECT
    p.id,
    p.slug,
    pt.title,
    p.view_count,
    p.published_at
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
WHERE p.status = 'published'
    AND pt.language_id = 1
    AND p.published_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY p.view_count DESC
LIMIT 10;

-- Get related posts (same category and tags)
SELECT DISTINCT
    p2.id,
    p2.slug,
    pt2.title,
    COUNT(DISTINCT pt2_tags.tag_id) AS shared_tags
FROM posts p1
JOIN posts p2 ON p1.category_id = p2.category_id AND p2.id != p1.id
JOIN post_translations pt2 ON p2.id = pt2.post_id
LEFT JOIN post_tags pt1_tags ON p1.id = pt1_tags.post_id
LEFT JOIN post_tags pt2_tags ON p2.id = pt2_tags.post_id
    AND pt1_tags.tag_id = pt2_tags.tag_id
WHERE p1.id = 100
    AND p2.status = 'published'
    AND pt2.language_id = 1
GROUP BY p2.id, p2.slug, pt2.title
ORDER BY shared_tags DESC, p2.published_at DESC
LIMIT 5;

Performance Tip: For multi-language sites, always filter by language_id early in the query to use indexes effectively and reduce the result set.

Post Revision System

Implementing version control for content:

-- Save revision before update
INSERT INTO post_revisions (post_id, language_id, user_id, title, content, revision_note)
SELECT
    post_id,
    language_id,
    1 AS user_id,
    title,
    content,
    'Before major edit'
FROM post_translations
WHERE post_id = 100 AND language_id = 1;

-- Get revision history
SELECT
    pr.id,
    pr.revision_note,
    pr.created_at,
    u.display_name AS revised_by,
    LENGTH(pr.content) AS content_length
FROM post_revisions pr
JOIN users u ON pr.user_id = u.id
WHERE pr.post_id = 100
    AND pr.language_id = 1
ORDER BY pr.created_at DESC
LIMIT 20;

-- Restore from revision
UPDATE post_translations pt
JOIN post_revisions pr ON pt.post_id = pr.post_id
    AND pt.language_id = pr.language_id
SET
    pt.title = pr.title,
    pt.content = pr.content
WHERE pr.id = 500;

Custom Post Meta Implementation

Using meta fields for flexible data:

-- Add custom field
INSERT INTO post_meta (post_id, meta_key, meta_value)
VALUES (100, 'subtitle', 'An interesting subtitle');

-- Get all meta for a post
SELECT meta_key, meta_value
FROM post_meta
WHERE post_id = 100;

-- Update meta value
UPDATE post_meta
SET meta_value = 'New value'
WHERE post_id = 100 AND meta_key = 'subtitle';

-- Query posts by meta value
SELECT
    p.id,
    pt.title,
    pm.meta_value AS reading_time
FROM posts p
JOIN post_translations pt ON p.id = pt.post_id
JOIN post_meta pm ON p.id = pm.post_id
WHERE pm.meta_key = 'reading_time'
    AND CAST(pm.meta_value AS UNSIGNED) > 10
    AND p.status = 'published'
    AND pt.language_id = 1;

Performance Warning: Post meta can slow queries if overused. For frequently queried fields, consider adding dedicated columns to the posts table instead.

Menu System Implementation

Building dynamic navigation menus:

-- Create menu
INSERT INTO menus (name, location) VALUES ('Main Navigation', 'header');
SET @menu_id = LAST_INSERT_ID();

-- Add menu items
INSERT INTO menu_items (menu_id, title, url, display_order)
VALUES
(@menu_id, 'Home', '/', 1),
(@menu_id, 'About', '/about', 2),
(@menu_id, 'Blog', '/blog', 3);

-- Add submenu items
INSERT INTO menu_items (menu_id, parent_id, title, url, display_order)
SELECT @menu_id, id, 'Web Development', '/blog/category/web', 1
FROM menu_items
WHERE menu_id = @menu_id AND title = 'Blog';

-- Get menu with hierarchy
WITH RECURSIVE menu_tree AS (
    SELECT
        mi.id,
        mi.parent_id,
        mi.title,
        mi.url,
        mi.display_order,
        0 AS depth,
        CAST(mi.display_order AS CHAR(500)) AS sort_path
    FROM menu_items mi
    JOIN menus m ON mi.menu_id = m.id
    WHERE m.location = 'header'
        AND mi.parent_id IS NULL

    UNION ALL

    SELECT
        mi.id,
        mi.parent_id,
        mi.title,
        mi.url,
        mi.display_order,
        mt.depth + 1,
        CONCAT(mt.sort_path, '-', mi.display_order)
    FROM menu_items mi
    JOIN menu_tree mt ON mi.parent_id = mt.id
)
SELECT * FROM menu_tree
ORDER BY sort_path;

Scheduled Posts Implementation

-- Create scheduled post
INSERT INTO posts (author_id, category_id, slug, status, published_at)
VALUES (1, 5, 'future-post', 'scheduled', '2024-12-31 10:00:00');

-- Get posts ready to publish (cron job query)
SELECT id, slug
FROM posts
WHERE status = 'scheduled'
    AND published_at <= NOW()
LIMIT 100;

-- Publish scheduled posts
UPDATE posts
SET status = 'published'
WHERE status = 'scheduled'
    AND published_at <= NOW();

Practice Exercise:

Task: Add a newsletter feature to the CMS.

Requirements:

Create tables for newsletter subscribers and campaigns
Track subscription status (active, unsubscribed)
Link posts to newsletter campaigns
Track email open rates and click rates

Solution:

CREATE TABLE newsletter_subscribers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100),
    status ENUM('pending', 'active', 'unsubscribed', 'bounced') DEFAULT 'pending',
    verification_token VARCHAR(100),
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unsubscribed_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE newsletter_campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    post_id BIGINT UNSIGNED NULL,
    status ENUM('draft', 'scheduled', 'sent') DEFAULT 'draft',
    sent_count INT UNSIGNED DEFAULT 0,
    open_count INT UNSIGNED DEFAULT 0,
    click_count INT UNSIGNED DEFAULT 0,
    scheduled_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE newsletter_tracking (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    subscriber_id BIGINT UNSIGNED NOT NULL,
    opened_at TIMESTAMP NULL,
    clicked_at TIMESTAMP NULL,
    FOREIGN KEY (campaign_id) REFERENCES newsletter_campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE,
    UNIQUE KEY unique_tracking (campaign_id, subscriber_id)
) ENGINE=InnoDB;

Summary

In this lesson, you learned:

How to design a complete blog and CMS database
Implementing multi-language content with translation tables
Building hierarchical taxonomies (categories with subcategories)
Creating a flexible post meta system for custom fields
Implementing post revisions for version control
Building dynamic menu systems
Handling scheduled posts and content workflow

Next Up: In the next lesson, we'll explore multi-tenant database design for SaaS applications!