Free preview
12 minDesigning the Database Schema
Design the production database schema for the Task Management API using entity relationships and normalization principles.
Entity Identification
Start by listing the core entities your system needs:
Users — people who log in Projects — a collection of tasks (e.g., "Website Redesign") ProjectMembers — who has access to which project (and at what role) Tasks — items of work within a project Comments — discussion on a taskThen identify relationships:
- A User can be a member of many Projects
- A Project has many Members (Users)
- A Project has many Tasks
- A Task belongs to one Project
- A Task can be assigned to one User
- A Task can have many Comments
- A Comment belongs to one User and one Task
The Schema in SQL
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE project_members (
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(20) DEFAULT 'member', -- 'owner', 'member', 'viewer'
PRIMARY KEY (project_id, user_id)
);
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
title VARCHAR(500) NOT NULL,
description TEXT,
status VARCHAR(20) DEFAULT 'todo', -- 'todo', 'in_progress', 'done'
priority VARCHAR(10) DEFAULT 'medium', -- 'low', 'medium', 'high'
due_date TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE comments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
author_id UUID REFERENCES users(id) ON DELETE CASCADE,
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);Multiple choice
Why is project_members modelled as a separate table rather than a column on projects?
- AA project has many members (many-to-many relationship) — this cannot be stored as a single column; a junction table is required
- BTo improve query performance
- CBecause PostgreSQL doesn't support arrays
- DTo avoid using JOINs
Multiple choice
What does ON DELETE CASCADE mean on the tasks.project_id foreign key?
- AWhen a project is deleted, all its tasks are automatically deleted too
- BDeleting a task also deletes the project
- CTasks are archived (soft deleted) when a project is deleted
- DDeletion is blocked if the project has any tasks
Fill in the blank
A project_members table that connects users and projects is called a ___ table (also known as a junction table).