KAHIBARO
Discord Login Register ☰
☰

29.3 Database Design

Table of Contents

Understanding the Database for a Task Management API

In this chapter you will design the database for a simple Task Management API. You do not need to be a SQL expert yet. The goal here is to understand what data you need, how it relates, and how to model it so that later chapters (CRUD, validation, etc.) are straightforward.

We will work with a relational database such as PostgreSQL, but the ideas apply to other systems too.

Requirements That Affect the Database

Before you design tables, you must understand what your application needs to store.

For a basic Task Management API, typical requirements are:

Even if the project requirements chapter does not mention all of these explicitly, it is useful to design for at least:

You can always start smaller, then extend.

Choosing Main Entities

An entity is a type of thing that your application stores, such as a User or a Task.

For this project, reasonable entities are:

We will focus on a clean, extensible design rather than an absolutely minimal one, because you are learning patterns used in real systems.

Designing the `users` Table

The users table stores basic account data.

A simple structure:

ColumnTypeDescription
idbigint (PK)Unique identifier for the user
emailvarchar, uniqueLogin and contact email
password_hashvarcharHashed password (never store raw)
full_namevarchar, nullableOptional display name
is_activebooleanWhether the account is active
created_attimestampWhen the user was created
updated_attimestampLast update time

Even though the main “Authentication” chapters come later, you need password_hash now so that you can link tasks to users.

Important rule: Never store plain text passwords in the database. Always store a secure password hash.

Typical primary key pattern:

Example simplified SQL:

sql 
CREATE TABLE users (
    id            BIGSERIAL PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255)        NOT NULL,
    full_name     VARCHAR(255),
    is_active     BOOLEAN             NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ         NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ         NOT NULL DEFAULT NOW()
);

Designing the `projects` Table

A project groups tasks. Each project belongs to one user.

ColumnTypeDescription
idbigint (PK)Unique project ID
owner_idbigint (FK)References users.id
namevarcharProject name
descriptiontext, nullableOptional description
is_archivedbooleanIf the project is archived
created_attimestampCreated time
updated_attimestampLast update time

The foreign key owner_id connects a project to its owner.

Example SQL:

sql 
CREATE TABLE projects (
    id          BIGSERIAL PRIMARY KEY,
    owner_id    BIGINT       NOT NULL REFERENCES users(id),
    name        VARCHAR(255) NOT NULL,
    description TEXT,
    is_archived BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

This structure lets you later implement endpoints like:

Designing the `tasks` Table

Tasks are the core of the application. Each task belongs to exactly 1 user, and optionally to a project.

Choosing columns

Typical columns:

ColumnTypeDescription
idbigint (PK)Unique task ID
owner_idbigint (FK)Owner user, references users.id
project_idbigint (FK, nullable)References projects.id, can be null
titlevarcharShort summary of the task
descriptiontext, nullableDetails
statusvarcharTask state, for example todo
prioritysmallint or varcharPriority level
due_datedate, nullableWhen the task is due
completed_attimestamp, nullableWhen it was finished
is_deletedbooleanFor soft delete
created_attimestampCreated time
updated_attimestampLast updated time

You could also use an enum type for status and priority, but for a first project, a simple text or smallint works fine.

Design tip: Always include created_at and updated_at on important tables. They help debugging, analytics, and audits.

Example SQL:

sql 
CREATE TABLE tasks (
    id           BIGSERIAL PRIMARY KEY,
    owner_id     BIGINT      NOT NULL REFERENCES users(id),
    project_id   BIGINT      REFERENCES projects(id),
    title        VARCHAR(255) NOT NULL,
    description  TEXT,
    status       VARCHAR(50)  NOT NULL DEFAULT 'todo',
    priority     SMALLINT     NOT NULL DEFAULT 0,
    due_date     DATE,
    completed_at TIMESTAMPTZ,
    is_deleted   BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

A simple priority scheme could be:

priority valueMeaning
0low
1medium
2high

You can map numbers to labels in your application code.

Designing the `tags` and `task_tags` Tables

Tags are a good example of a many to many relationship. One task can have many tags, and one tag can belong to many tasks.

`tags` table

ColumnTypeDescription
idbigint (PK)Unique tag ID
owner_idbigint (FK)Tag owner, references users.id
namevarcharExample: "work", "urgent"
created_attimestampCreated time
updated_attimestampLast updated

You might want each user to have their own namespace of tags, so two users can both have a "work" tag without conflict.

sql 
CREATE TABLE tags (
    id         BIGSERIAL PRIMARY KEY,
    owner_id   BIGINT       NOT NULL REFERENCES users(id),
    name       VARCHAR(50)  NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (owner_id, name)
);

The UNIQUE (owner_id, name) constraint prevents a single user from creating the same tag twice.

`task_tags` join table

This table connects tasks and tags.

ColumnTypeDescription
task_idbigint (FK)References tasks.id
tag_idbigint (FK)References tags.id

You normally use a composite primary key:

sql 
CREATE TABLE task_tags (
    task_id BIGINT NOT NULL REFERENCES tasks(id),
    tag_id  BIGINT NOT NULL REFERENCES tags(id),
    PRIMARY KEY (task_id, tag_id)
);

This ensures that the same tag is not linked twice to the same task.

Relationships Summary

So far you have these relationships:

From tableColumnTo tableRelationship type
projectsowner_idusersMany projects per user
tasksowner_idusersMany tasks per user
tasksproject_idprojectsMany tasks per project
tagsowner_idusersMany tags per user
task_tagstask_idtasksMany tags per task
task_tagstag_idtagsMany tasks per tag

In more familiar names:

This structure is enough for a realistic Task Management API.

Example: How API Endpoints Map to the Schema

Here are some typical endpoints and how they use the database design.

Create a task

Endpoint:

http 
POST /tasks
Content-Type: application/json
{
  "title": "Buy groceries",
  "description": "Milk, eggs, bread",
  "project_id": 3,
  "priority": 1,
  "due_date": "2026-09-01",
  "tags": ["personal", "errand"]
}

What happens in the database:

  1. Insert into tasks:
sql 
   INSERT INTO tasks (owner_id, project_id, title, description, priority, due_date)
   VALUES (:user_id, 3, 'Buy groceries', 'Milk, eggs, bread', 1, '2026-09-01');
  1. For each tag name:
    • Check if that tag exists for the user in tags. If not, insert it.
    • Insert into task_tags with the new task id and tag id.

List tasks in a project

Endpoint:

http 
GET /projects/3/tasks

Query:

sql 
SELECT *
FROM tasks
WHERE project_id = 3
  AND owner_id = :user_id
  AND is_deleted = FALSE
ORDER BY created_at DESC;

Mark a task as completed

Endpoint:

http 
PATCH /tasks/42
Content-Type: application/json
{
  "status": "done"
}

Update:

sql 
UPDATE tasks
SET status = 'done',
    completed_at = NOW(),
    updated_at = NOW()
WHERE id = 42
  AND owner_id = :user_id
  AND is_deleted = FALSE;

Note how the schema makes these operations easy to express.

Soft Deletes vs Hard Deletes

Often you do not want to remove tasks permanently. Instead you mark them as deleted.

We already added is_deleted to tasks. The idea:

Example soft delete:

sql 
UPDATE tasks
SET is_deleted = TRUE,
    updated_at = NOW()
WHERE id = 42
  AND owner_id = :user_id;

If you truly need to remove all traces, you can later run a background job to hard delete tasks where is_deleted = TRUE and older than some time.

Rule: Never forget to filter out soft deleted rows in normal queries, or deleted data will reappear in your API responses.

Indexes for Performance

Indexes help queries run faster, especially when you filter or sort by specific columns.

For this project, common useful indexes are:

sql 
  CREATE INDEX idx_tasks_owner_project
      ON tasks (owner_id, project_id);
sql 
  CREATE INDEX idx_tasks_owner_status
      ON tasks (owner_id, status);
sql 
  CREATE UNIQUE INDEX idx_tags_owner_name
      ON tags (owner_id, name);

You can add more indexes later when you measure performance, but these are a good starting point.

Normalization Basics in This Design

Without going deep into the dedicated "Normalization" chapter, you can already see some normalization ideas here:

This keeps data consistent, and changes in one place, for example renaming a tag, are reflected everywhere.

Example Final Schema Overview

Here is a simplified overview of the core tables:

sql 
-- Users
CREATE TABLE users (
    id            BIGSERIAL PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255)        NOT NULL,
    full_name     VARCHAR(255),
    is_active     BOOLEAN             NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ         NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ         NOT NULL DEFAULT NOW()
);
-- Projects
CREATE TABLE projects (
    id          BIGSERIAL PRIMARY KEY,
    owner_id    BIGINT       NOT NULL REFERENCES users(id),
    name        VARCHAR(255) NOT NULL,
    description TEXT,
    is_archived BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
-- Tasks
CREATE TABLE tasks (
    id           BIGSERIAL PRIMARY KEY,
    owner_id     BIGINT       NOT NULL REFERENCES users(id),
    project_id   BIGINT       REFERENCES projects(id),
    title        VARCHAR(255) NOT NULL,
    description  TEXT,
    status       VARCHAR(50)  NOT NULL DEFAULT 'todo',
    priority     SMALLINT     NOT NULL DEFAULT 0,
    due_date     DATE,
    completed_at TIMESTAMPTZ,
    is_deleted   BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
-- Tags
CREATE TABLE tags (
    id         BIGSERIAL PRIMARY KEY,
    owner_id   BIGINT      NOT NULL REFERENCES users(id),
    name       VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (owner_id, name)
);
-- Many to many: tasks <-> tags
CREATE TABLE task_tags (
    task_id BIGINT NOT NULL REFERENCES tasks(id),
    tag_id  BIGINT NOT NULL REFERENCES tags(id),
    PRIMARY KEY (task_id, tag_id)
);

This design is:

In the next chapters, you will use this schema to implement operations like creating tasks, listing tasks by project, and managing tags through your API.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3133 / 24h
Impressum / Imprint
Datenschutz / Data Policy