Design the Schema - Planning Your Database
Introduction
Before writing any CREATE TABLE statements, plan your schema! A good design prevents headaches later. This lesson guides you through designing the task management database schema.
Entity-Relationship Diagram (ERD)
Here's the complete schema design:
users (1) ─────< workspace_members >───── (M) workspaces
│
│ (1)
│
↓ (M)
projects
│
│ (1)
│
↓ (M)
tasks
/ │ \
/ │ \
/ │ \
task_assignments comments task_tags
│ │ │
↓ ↓ ↓
users users tags
Core Tables
1. users
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),
bio TEXT,
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 ~ '^[^@]+@[^@]+\.[^@]+$')
);
2. workspaces
CREATE TABLE workspaces (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
owner_id BIGINT NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
3. workspace_members
CREATE TABLE workspace_members (
workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(20) DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
joined_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (workspace_id, user_id)
);
4. projects
CREATE TABLE projects (
id BIGSERIAL PRIMARY KEY,
workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
name VARCHAR(200) NOT NULL,
description TEXT,
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'archived', 'completed')),
created_by BIGINT REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
5. tasks
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
description TEXT,
status VARCHAR(20) DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done', 'cancelled')),
priority VARCHAR(10) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high')),
due_date DATE,
created_by BIGINT REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ,
deleted_at TIMESTAMPTZ
);
6. task_assignments
CREATE TABLE task_assignments (
task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
assigned_at TIMESTAMPTZ DEFAULT NOW(),
assigned_by BIGINT REFERENCES users(id),
PRIMARY KEY (task_id, user_id)
);
7. tags
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
name VARCHAR(50) NOT NULL,
color VARCHAR(7) DEFAULT '#808080',
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (workspace_id, name)
);
8. task_tags
CREATE TABLE task_tags (
task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (task_id, tag_id)
);
9. comments
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(id),
content TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
10. activity_log
CREATE TABLE activity_log (
id BIGSERIAL PRIMARY KEY,
workspace_id BIGINT REFERENCES workspaces(id) ON DELETE CASCADE,
user_id BIGINT REFERENCES users(id),
action VARCHAR(50) NOT NULL,
entity_type VARCHAR(50),
entity_id BIGINT,
details JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
Design Decisions Explained
Primary Keys
- BIGSERIAL for all ID columns (future-proof)
- Composite keys for junction tables (workspace_members, task_assignments, task_tags)
Foreign Keys
- ON DELETE CASCADE for dependent data (comments, assignments)
- References users(id) without CASCADE for creators (preserve history)
Timestamps
- created_at and updated_at on all main tables
- TIMESTAMPTZ for timezone awareness
Soft Deletes
- deleted_at on users, workspaces, projects, tasks, comments
- Allows data recovery and audit trail
Constraints
- CHECK for enum-like values (status, priority, role)
- UNIQUE for business keys (workspace/tag name combination)
- NOT NULL for required fields
Indexes (Next Lesson)
We'll add indexes on:
- Foreign keys
- Frequently queried columns (status, due_date)
- Composite indexes for common query patterns
Next Steps
In Lesson 8.3, you'll:
- Write the complete schema as SQL
- Create all tables
- Insert sample data for testing
Copy this schema into your 01-schema.sql file and get ready to build!
اختبار قصير
السؤال 1 من 520% مكتمل
0 من 5 أسئلة تمت الإجابة عنها
التالي · الدرس 40 من 44
Loading Sample Data
قراءة 4 دقيقة
أنهيت 39 من 44 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

