Capstone Project Overview - Building a Task Management System

Welcome to the Capstone!

Congratulations on making it to the final module! You've learned SQL fundamentals, JOINs, aggregation, advanced queries, and database design. Now it's time to build something real.

Project: Task Management System

You'll design and build a complete database for a collaborative task management application (think Trello, Asana, or Todoist).

Features

Your system will support:

• ✅ Users and Authentication

• User accounts with email/password

• User profiles with bio and avatar

• ✅ Workspaces (Teams)

• Users can create multiple workspaces

• Invite other users to workspaces

• Different roles (owner, admin, member)

• ✅ Projects within Workspaces

• Each workspace has multiple projects

• Projects have names, descriptions, and statuses

• ✅ Tasks

• Create, assign, and track tasks

• Task priorities (low, medium, high)

• Task statuses (todo, in_progress, done)

• Due dates

• Task comments and activity

• ✅ Tags and Labels

• Organize tasks with tags

• Filter tasks by tags

• ✅ Activity Log

• Track all changes (who did what, when)

Learning Objectives

By completing this project, you will:

• ✅ Design a normalized database schema from scratch
• ✅ Define appropriate primary keys, foreign keys, and constraints
• ✅ Create indexes for optimal query performance
• ✅ Write complex queries involving multiple JOINs
• ✅ Use aggregate functions for reporting
• ✅ Implement soft deletes and audit trails
• ✅ Apply best practices learned throughout the course

Project Structure

This capstone is divided into 7 lessons:

Lesson 8.1: Project Overview (This Lesson)

Introduction to the project and requirements.

Lesson 8.2: Design the Schema

Design tables, relationships, and constraints on paper (ERD).

Lesson 8.3: Create Tables and Load Sample Data

Write CREATE TABLE statements and insert test data.

Lesson 8.4: Business Queries

Write SQL queries to solve real-world business questions.

Lesson 8.5: Reports and Analytics

Create dashboards and summary reports using aggregation.

Lesson 8.6: Performance Optimization

Add indexes and analyze query performance.

Lesson 8.7: Final Review and Next Steps

Review your work and explore advanced SQL topics.

Database Requirements

Entities (Tables)

1. users: User accounts
2. workspaces: Team workspaces
3. workspace_members: User membership in workspaces
4. projects: Projects within workspaces
5. tasks: Individual tasks
6. task_assignments: Assign tasks to users
7. tags: Task labels
8. task_tags: Many-to-many relationship
9. comments: Task comments
10. activity_log: Audit trail of all actions

Relationships

• Users create workspaces (one-to-many)
• Users belong to workspaces (many-to-many via workspace_members)
• Workspaces contain projects (one-to-many)
• Projects contain tasks (one-to-many)
• Tasks can be assigned to users (many-to-many via task_assignments)
• Tasks have tags (many-to-many via task_tags)
• Tasks have comments (one-to-many)

Sample Queries You'll Write

By the end of this module, you'll be able to answer:

1. "Which tasks are assigned to me and due this week?"
2. "Show all tasks in a project, grouped by status"
3. "Who are the most active users in a workspace?"
4. "What's the completion rate for each project?"
5. "Show task activity log for the last 7 days"
6. "Find overdue tasks assigned to specific users"
7. "Generate a report of tasks by priority and assignee"

Getting Started

Tools You'll Need

• PostgreSQL installed
• A database client (pgAdmin, DBeaver, psql, or TablePlus)
• A text editor for SQL scripts

Project Files

You can organize your work in separate .sql files:

task-management/
├── 01-schema.sql          # CREATE TABLE statements
├── 02-indexes.sql         # CREATE INDEX statements
├── 03-sample-data.sql     # INSERT test data
├── 04-queries.sql         # Business queries
└── 05-reports.sql         # Analytics and reports

Tips for Success

• ✅ Start simple, iterate: Begin with core tables, add complexity later
• ✅ Think about real-world use: How would users actually use this system?
• ✅ Test as you go: Insert sample data early to validate your design
• ✅ Use meaningful names: Clear table and column names make queries easier
• ✅ Add constraints: NOT NULL, FOREIGN KEY, CHECK constraints prevent bad data
• ✅ Index wisely: Add indexes on foreign keys and frequently queried columns
• ✅ Document your work: Add comments explaining design decisions

Example: Starting Point

Here's a simple users table to get you started:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    avatar_url VARCHAR(500),
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active) WHERE deleted_at IS NULL;

What's Next?

In Lesson 8.2, you'll:

• Design the complete database schema
• Create an Entity-Relationship Diagram (ERD)
• Define all tables, columns, and relationships
• Plan constraints and indexes

Let's start building!

Key Takeaways

• ✅ Capstone project: Task management system
• ✅ 10+ tables with complex relationships
• ✅ Real-world features: Authentication, teams, projects, tasks
• ✅ 7 lessons to complete the project
• ✅ Hands-on practice applying everything you've learned
• ✅ Portfolio piece to showcase your SQL skills

Ready?

Take a moment to set up your environment, create a new database, and get ready to design an amazing project!

-- Create your database
CREATE DATABASE task_management;

-- Connect to it
\c task_management

Next: Lesson 8.2 - Design the Schema