Create Tables and Load Sample Data
Create the Database
CREATE DATABASE task_management;
\c task_management
Complete Schema Script
Save this as 01-schema.sql:
-- Users table
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 ~ '^[^@]+@[^@]+\.[^@]+$')
);
-- Workspaces table
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
);
-- Workspace members table
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)
);
-- Projects table
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
);
-- Tasks table
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
);
-- Task assignments table
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)
);
-- Tags table
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)
);
-- Task tags table
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)
);
-- Comments table
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
);
-- Activity log table
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()
);
Run: psql -d task_management -f 01-schema.sql
Sample Data Script
Save this as 03-sample-data.sql:
-- Insert users
INSERT INTO users (email, password_hash, first_name, last_name, bio) VALUES
('alice@example.com', '$2b$10$...hash...', 'Alice', 'Smith', 'Project Manager'),
('bob@example.com', '$2b$10$...hash...', 'Bob', 'Jones', 'Developer'),
('carol@example.com', '$2b$10$...hash...', 'Carol', 'White', 'Designer'),
('dave@example.com', '$2b$10$...hash...', 'Dave', 'Wilson', 'QA Engineer');
-- Insert workspaces
INSERT INTO workspaces (name, description, owner_id) VALUES
('Acme Corp', 'Main company workspace', 1),
('Side Project', 'Weekend project workspace', 2);
-- Insert workspace members
INSERT INTO workspace_members (workspace_id, user_id, role) VALUES
(1, 1, 'owner'),
(1, 2, 'admin'),
(1, 3, 'member'),
(1, 4, 'member'),
(2, 2, 'owner'),
(2, 3, 'member');
-- Insert projects
INSERT INTO projects (workspace_id, name, description, created_by) VALUES
(1, 'Website Redesign', 'Complete website overhaul', 1),
(1, 'Mobile App', 'iOS and Android apps', 1),
(2, 'Personal Blog', 'Build a personal blog', 2);
-- Insert tasks
INSERT INTO tasks (project_id, title, description, status, priority, due_date, created_by) VALUES
(1, 'Design homepage mockup', 'Create Figma design', 'done', 'high', '2024-12-15', 1),
(1, 'Implement responsive layout', 'CSS Grid and Flexbox', 'in_progress', 'high', '2024-12-20', 1),
(1, 'Write content for About page', 'Company history and team', 'todo', 'medium', '2024-12-25', 1),
(2, 'Set up React Native project', 'Initialize with Expo', 'done', 'high', '2024-12-10', 2),
(2, 'Design app navigation', 'Bottom tabs and stack navigator', 'in_progress', 'medium', '2024-12-18', 2),
(3, 'Choose blogging platform', 'Research options', 'todo', 'low', '2025-01-05', 2);
-- Insert task assignments
INSERT INTO task_assignments (task_id, user_id, assigned_by) VALUES
(1, 3, 1), -- Carol assigned to design
(2, 2, 1), -- Bob assigned to implementation
(3, 1, 1), -- Alice assigned to content
(4, 2, 2), -- Bob assigned to setup
(5, 3, 2); -- Carol assigned to design
-- Insert tags
INSERT INTO tags (workspace_id, name, color) VALUES
(1, 'frontend', '#3B82F6'),
(1, 'backend', '#10B981'),
(1, 'design', '#F59E0B'),
(1, 'urgent', '#EF4444'),
(2, 'research', '#8B5CF6');
-- Insert task tags
INSERT INTO task_tags (task_id, tag_id) VALUES
(1, 3), -- Design tag
(2, 1), -- Frontend tag
(3, 1), -- Frontend tag
(4, 2), -- Backend tag
(5, 3); -- Design tag
-- Insert comments
INSERT INTO comments (task_id, user_id, content) VALUES
(1, 1, 'Great work on the mockup! Looks amazing.'),
(1, 3, 'Thanks! Let me know if you want any changes.'),
(2, 2, 'Working on mobile-first approach.'),
(2, 1, 'Perfect, that''s the right strategy.');
-- Insert activity log
INSERT INTO activity_log (workspace_id, user_id, action, entity_type, entity_id) VALUES
(1, 1, 'created_project', 'project', 1),
(1, 1, 'created_task', 'task', 1),
(1, 3, 'completed_task', 'task', 1),
(1, 2, 'commented', 'task', 2);
Run: psql -d task_management -f 03-sample-data.sql
Verify Data Loaded
-- Check row counts
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'workspaces', COUNT(*) FROM workspaces
UNION ALL SELECT 'projects', COUNT(*) FROM projects
UNION ALL SELECT 'tasks', COUNT(*) FROM tasks;
-- View sample tasks
SELECT t.id, t.title, t.status, u.first_name || ' ' || u.last_name AS assigned_to
FROM tasks t
LEFT JOIN task_assignments ta ON t.id = ta.task_id
LEFT JOIN users u ON ta.user_id = u.id;
Next Steps
In Lesson 8.4, you'll write business queries to answer real-world questions using this data!
اختبار قصير
السؤال 1 من 520% مكتمل
0 من 5 أسئلة تمت الإجابة عنها
التالي · الدرس 41 من 44
Common Business Queries
قراءة 3 دقيقة
أنهيت 40 من 44 درسًا

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

