Module 10 — Portfolio Project: Blog Database

Project Overview

Our goal is to build the backend database for a professional blogging platform.

Requirements:

• Users: Secure storage for authors and admins.
• Posts: Articles with SEO-friendly slugs and publishing workflows.
• Comments: Reader engagement with moderation states.
• Categories & Tags: Two ways to organize content (One-to-Many and Many-to-Many).

Blog Architecture Flow

Step 1: Design the Schema (ER Diagram)

A professional design starts with a clear map of how tables connect.

Blog Database Schema

Entity Relationship (Mermaid)

Step 2: Create the Database & Tables

Run these commands in your psql shell.

-- 1. Create the Database
-- CREATE DATABASE blog_db;
-- \c blog_db
 
-- 2. Users Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash 

Step 3: Populate with Professional Data

-- Insert Users
INSERT INTO users (username, email, password_hash, role) VALUES
    ('alice_dev', 'alice@example.com', 'hash_123', 'admin'),
    ('bob_codes', 'bob@example.com', 'hash_456', 'author');
 
--

Step 4: Real-World Dashboard Queries

In a real app, these queries would power the Homepage and Admin Dashboard.

4.1 Latest Published Posts (Homepage Feed)

SELECT 
    posts.title, 
    users.username AS author, 
    categories.name AS category,
    posts.published_at
FROM posts
JOIN users ON posts.user_id = users.id
JOIN categories ON posts.category_id = categories.id
WHERE

4.2 Post Engagement (Comment Count)

SELECT 
    posts.title, 
    COUNT(comments.id) AS total_comments
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id, posts.title
ORDER BY total_comments DESC;

Step 5: Production Optimization (Indexes)

Queries get slow as the database grows. We add Indexes to speed them up.

-- Speed up looking up posts by their URL slug
CREATE INDEX idx_posts_slug ON posts(slug);
 
-- Speed up filtering by published date
CREATE INDEX idx_posts_published ON posts(published_at) WHERE published = true;

🏆 Final Challenge

To truly test your skills, try to write these queries on your own:

1. Tag Search: Find all posts that have the tag 'sql'.
2. Draft List: Find all posts by 'Alice' that are NOT yet published.
3. Admin Audit: Show a list of all comments along with the username of the person who wrote it and the title of the post it belongs to.
4. Content Strategy: Which category has the most published posts?

🎉 What You've Accomplished