How to Design a Database Schema for a Task Manager App

Introduction

When building a Task Manager application (like Todoist or Asana), one of the first and most critical steps is designing a well-structured database schema.

In this tutorial, we’ll walk through how to design a relational schema for a Task Manager using MySQL. You’ll learn how to define tables for users, projects, tasks, comments, and attachments, along with the relationships between them.

By the end, you’ll have a schema that supports:

• User registration and authentication
• Project and task management
• Comments and file attachments
• Task status, priority, and due dates

Step 1: Identify Entities

A Task Manager typically has these core entities:

Step 2: Define Relationships

Step 3: Create Database Schema &Tables

In your DBMS (Database Management System) Create the database and tables.

CREATE DATABASE IF NOT EXISTS task_manager_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE task_manager_db;

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE projects (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tasks (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
  priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
  due_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE comments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE attachments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT NOT NULL,
  file_url VARCHAR(255) NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
);

CREATE TABLE labels (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE task_labels (
  task_id BIGINT NOT NULL,
  label_id BIGINT NOT NULL,
  PRIMARY KEY (task_id, label_id),
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (label_id) REFERENCES labels(id) ON DELETE CASCADE
);

Step 4: ER Diagram Overview

This ER (Entity Relationship) Diagram helps visualize the relationships between tables in your schema. View this Task Manager ER Diagram on drawSQL.

Step 5: Example SQL Queries

Insert a new project

INSERT INTO projects (user_id, name, description)
VALUES (1, 'Personal Productivity', 'Tasks for daily planning');

Add a task to the project

INSERT INTO tasks (project_id, title, description, priority, due_date)
VALUES (1, 'Write blog article', 'Write a post about MySQL schema design', 'high', '2025-11-10');

Mark a task as completed

UPDATE tasks
SET status = 'completed'
WHERE id = 3;

Get all tasks with labels

SELECT t.title, l.name AS label
FROM tasks t
JOIN task_labels tl ON t.id = tl.task_id
JOIN labels l ON tl.label_id = l.id;

Step 6: SQL Practice Exercises

SELECT name, email FROM users;

SELECT p.name, p.description
FROM projects p
JOIN users u ON p.user_id = u.id
WHERE u.name = 'Alice Johnson';

Summary

In this tutorial, we designed a normalized MySQL schema for a Task Manager app.
We covered:

• Entity and relationship identification
• Table creation scripts
• Relationship mapping
• Example SQL queries

This schema is modular and scalable, making it easy to extend with features like teams, notifications, and task assignments.

Other Schemas

Read our guide on How to Design a Database Schema for an Event Ticket App to learn more database design fundamentals.

Resources

Download the complete schema from our SQL Examples repository on GitHub.

Create and edit your own Task Manager ER diagram using DrawSQL, a free web-based tool for designing relational databases.

Download MySQL Workbench, a visual tool to design, model, and manage MySQL databases locally.