KAHIBARO
Discord Login Register ☰
☰

32.3. Designing the Database

Table of Contents

From Requirements to Tables

Designing the database for a production backend is about turning business requirements into a clear, consistent, and efficient data model.

In this chapter we will focus on how to design the database for the final project, not on SQL syntax or specific PostgreSQL features which are covered elsewhere.

You can imagine your final project is something like a realistic REST API (for example, a task app, e‑commerce, or SaaS product). The design process is the same for all of them.

Key idea: Database design is about modeling real-world concepts as data, choosing correct relationships, and applying normalization so your data stays consistent and your queries stay fast.

We will go step by step from requirements to entities, relationships, and a normalized schema, and we will highlight decisions that matter in production.

Step 1: Clarify Requirements in Data Terms

Before touching tables, translate requirements into data questions.

Examples of requirement-to-data translations:

RequirementData question
Users can register and log inHow do we store users, emails, password hashes, and verification state?
Users can create and update items/tasks/ordersWhat is the main resource table? Who owns each record?
Each item belongs to a category or projectDo we need a categories or projects table and foreign keys to it?
We need audit or historyWhich actions must be tracked, at what level of detail, and for how long?
We need permissions / rolesHow do we represent roles and link users to them?
We need performance and reportingWhat queries, filters, and aggregations must be fast?

Create a requirements-to-data list for your project:

Step 2: Identify Entities and Attributes

An entity is a thing you store data about. In relational databases, entities usually become tables.

For a typical final project, you might have:

For each entity, list attributes and their types in a simple table.

Example for a task management style project:

EntityAttributes (examples)
Userid, email, password_hash, full_name, is_active, created_at, updated_at
Projectid, owner_id (user), name, description, is_archived, created_at, updated_at
Taskid, project_id, assignee_id, title, description, status, priority, due_date, created_at, updated_at
Commentid, task_id, author_id, body, created_at

Do not optimize early. Just capture what exists and how it is used.

Step 3: Choose Primary Keys

Every row must be uniquely identifiable.

You can choose:

In most backends, surrogate keys are preferred.

Rule: Use a simple surrogate key as the primary key for most tables (usually BIGSERIAL or UUID), and put unique constraints on natural keys like email where needed.

Example:

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

Step 4: Define Relationships

Relationships describe how tables connect.

Typical relationship patterns:

Relationship typeExampleImplementation idea
One-to-manyUser has many projectsprojects.user_id references users.id
Many-to-oneMany tasks belong to a projecttasks.project_id references projects.id
Many-to-manyUsers can belong to many projects and vice versaJoin table project_members(user_id, project_id, role)
One-to-oneUser has one profileuser_profiles.user_id UNIQUE FK to users.id

Define relationships first conceptually, then as foreign keys.

Example relationships for a project with users, projects, and tasks:

In SQL:

sql 
CREATE TABLE projects (
    id          BIGSERIAL PRIMARY KEY,
    owner_id    BIGINT NOT NULL REFERENCES users(id),
    name        TEXT 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()
);
CREATE TABLE tasks (
    id          BIGSERIAL PRIMARY KEY,
    project_id  BIGINT NOT NULL REFERENCES projects(id),
    assignee_id BIGINT REFERENCES users(id),
    title       TEXT NOT NULL,
    description TEXT,
    status      TEXT NOT NULL,
    priority    TEXT NOT NULL,
    due_date    DATE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Step 5: Handle Many-to-Many Relationships

If you find sentences containing “many … many” you usually need a join table.

Example: Users can be members of many projects, and projects can have many members.

Create a join table:

sql 
CREATE TABLE project_members (
    project_id  BIGINT NOT NULL REFERENCES projects(id),
    user_id     BIGINT NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL,  -- e.g. "owner", "member", "viewer"
    added_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (project_id, user_id)
);

Using a composite primary key here:

If you need to reference membership records themselves from other tables, then add a surrogate id and keep a UNIQUE (project_id, user_id) constraint.

Step 6: Apply Normalization

Normalization helps avoid duplication and inconsistencies.

You do not need to memorize all theoretical normal forms, but you should follow a few practical rules.

Rule 1 (First Normal Form, 1NF):
Each column holds a single atomic value, and each row-column intersection holds exactly one value, not lists, arrays, or repeated fields.

Examples of 1NF violations:

Better designs:

sql 
-- Separate columns
ALTER TABLE users ADD COLUMN street TEXT;
ALTER TABLE users ADD COLUMN city TEXT;
ALTER TABLE users ADD COLUMN postcode TEXT;
-- Separate table for tags
CREATE TABLE task_tags (
    task_id BIGINT NOT NULL REFERENCES tasks(id),
    tag     TEXT NOT NULL,
    PRIMARY KEY (task_id, tag)
);

Rule 2 (Second Normal Form, 2NF):
Every non-key column should depend on the whole primary key, not just part of it.

This mainly matters when using composite primary keys. If you add columns that only depend on part of a composite key, you may need another table.

Example mistake:

sql 
CREATE TABLE project_members (
    project_id  BIGINT,
    user_id     BIGINT,
    user_email  TEXT,      -- depends only on user_id, not on both
    PRIMARY KEY (project_id, user_id)
);

user_email depends only on user_id. It should live in users, not here.

Rule 3 (Third Normal Form, 3NF):
Non-key columns must not depend on other non-key columns. They should depend only on the primary key.

Example mistake:

sql 
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    domain      TEXT NOT NULL  -- derived from email, e.g. part after '@'
);

domain depends on email, not on id. It is better to compute domain in your application or views when needed, rather than store it.

Normalization is about avoiding anomalies:

If you see these problems, your schema probably needs normalization.

Step 7: Represent Status, Enums, and Types

Many tables need status fields or type flags.

You have several options:

OptionExampleProsCons
Free-text (TEXT)status TEXT like "open", "done"Very flexible, easy to changeTypos, no strong validation
Constrained textstatus TEXT CHECK (status IN (...))Validated at DB level, still human-readableNeed migrations when adding values
Numeric codestatus SMALLINT with meaning in appCompact, possibly fasterHarder to read without reference
Enum typePostgreSQL ENUMStrong typingAltering enum type needs migrations
Lookup tableSeparate task_statuses tableFlexible, can add metadataJoin or app-level mapping needed

For a backend that you control fully, a constrained TEXT or PostgreSQL ENUM is usually enough.

Example with CHECK constraint:

sql 
CREATE TABLE tasks (
    id          BIGSERIAL PRIMARY KEY,
    project_id  BIGINT NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL CHECK (status IN ('todo', 'in_progress', 'done', 'archived')),
    priority    TEXT NOT NULL CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Step 8: Add Timestamps and Soft Deletes

Most production schemas include standard columns:

Soft delete means you mark rows as deleted without actually removing them.

Rule: Prefer soft deletes for important business data, and use a boolean or timestamp flag. Implement hard deletes only when you are sure the data should vanish.

Example:

sql 
CREATE TABLE tasks (
    id          BIGSERIAL PRIMARY KEY,
    project_id  BIGINT NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

In your queries, you then filter out deleted records:

sql 
SELECT * FROM tasks
WHERE project_id = $1 AND deleted_at IS NULL;

Step 9: Design for Authentication and Authorization Data

Your final project will have some form of authentication and authorization. Typical tables:

Example minimal user and token tables:

sql 
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    full_name       TEXT,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE refresh_tokens (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id),
    token           TEXT NOT NULL UNIQUE,
    user_agent      TEXT,
    ip_address      INET,
    revoked         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL
);

For role based access control:

sql 
CREATE TABLE roles (
    id      BIGSERIAL PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE
);
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id),
    role_id BIGINT NOT NULL REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

Step 10: Plan for Files and External Storage

If your project needs file uploads, design how to reference them.

A common pattern is to store only metadata in the database and the actual file in an object storage such as S3 or another service.

Example:

sql 
CREATE TABLE files (
    id              BIGSERIAL PRIMARY KEY,
    owner_id        BIGINT REFERENCES users(id),
    original_name   TEXT NOT NULL,
    content_type    TEXT NOT NULL,
    size_bytes      BIGINT NOT NULL,
    storage_key     TEXT NOT NULL,  -- path or key in object storage
    checksum        TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Other tables then reference files.id. For example:

sql 
ALTER TABLE tasks ADD COLUMN attachment_id BIGINT REFERENCES files(id);

Step 11: Think About Indexes Early

Performance tuning is covered in other chapters, but your design should anticipate indexing needs.

Ask yourself:

Example useful indexes:

sql 
-- Often you will query tasks by project
CREATE INDEX idx_tasks_project_id ON tasks(project_id);
-- If you filter by project + status
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
-- If you search by email frequently (already UNIQUE)
CREATE UNIQUE INDEX idx_users_email ON users(email);

Rule: Index columns that are heavily used in WHERE, JOIN, and ORDER BY clauses, but avoid indexing everything, because each index slows down writes.

Step 12: Avoid Common Design Pitfalls

Some patterns look convenient but cause trouble later.

Putting Everything in One Table

Example bad idea:

sql 
CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    type TEXT NOT NULL,  -- "user", "project", "task"
    data JSONB NOT NULL
);

This makes querying, constraints, and migrations hard.

Instead, use separate tables for distinct entities, and use JSONB only for flexible, secondary data that does not need strict structure.

Embedding Lists as Strings

Example bad idea:

sql 
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    labels TEXT  -- "bug,frontend,urgent"
);

You will struggle with filtering and integrity. Use a separate table for labels.

Duplicating Data Without Need

Sometimes people copy user info into many tables "for convenience":

sql 
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    task_id BIGINT NOT NULL REFERENCES tasks(id),
    author_id BIGINT NOT NULL REFERENCES users(id),
    author_email TEXT NOT NULL,  -- duplicate
    author_name  TEXT NOT NULL,  -- duplicate
    body         TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

If the email or name changes, you have to update many places.

Better: keep only author_id here and join with users when needed. If you really need to snapshot the name at comment time, make it explicit in your design and document why.

Step 13: Example Schema for a Final Project

To make it concrete, here is a small but realistic schema for a production-ready task or project management backend.

Core tables

sql 
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    full_name       TEXT,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE projects (
    id              BIGSERIAL PRIMARY KEY,
    owner_id        BIGINT NOT NULL REFERENCES users(id),
    name            TEXT 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()
);
CREATE TABLE project_members (
    project_id      BIGINT NOT NULL REFERENCES projects(id),
    user_id         BIGINT NOT NULL REFERENCES users(id),
    role            TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    added_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (project_id, user_id)
);
CREATE TABLE tasks (
    id              BIGSERIAL PRIMARY KEY,
    project_id      BIGINT NOT NULL REFERENCES projects(id),
    assignee_id     BIGINT REFERENCES users(id),
    title           TEXT NOT NULL,
    description     TEXT,
    status          TEXT NOT NULL CHECK (status IN ('todo', 'in_progress', 'blocked', 'done')),
    priority        TEXT NOT NULL CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    due_date        DATE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ
);
CREATE TABLE comments (
    id              BIGSERIAL PRIMARY KEY,
    task_id         BIGINT NOT NULL REFERENCES tasks(id),
    author_id       BIGINT NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Authentication-related tables

sql 
CREATE TABLE refresh_tokens (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id),
    token           TEXT NOT NULL UNIQUE,
    user_agent      TEXT,
    ip_address      INET,
    revoked         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL
);
CREATE TABLE email_verification_tokens (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(id),
    token           TEXT NOT NULL UNIQUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL,
    used_at         TIMESTAMPTZ
);

Supporting tables

sql 
CREATE TABLE files (
    id              BIGSERIAL PRIMARY KEY,
    owner_id        BIGINT REFERENCES users(id),
    original_name   TEXT NOT NULL,
    content_type    TEXT NOT NULL,
    size_bytes      BIGINT NOT NULL,
    storage_key     TEXT NOT NULL,
    checksum        TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE task_attachments (
    task_id         BIGINT NOT NULL REFERENCES tasks(id),
    file_id         BIGINT NOT NULL REFERENCES files(id),
    PRIMARY KEY (task_id, file_id)
);

Indexes for common queries

sql 
CREATE INDEX idx_projects_owner_id ON projects(owner_id);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
CREATE INDEX idx_comments_task_id ON comments(task_id);

Step 14: Validate the Design with Example Queries

Use real use cases to test your design.

Example requirements and simple SQL to check:

  1. "List all active tasks in a project, ordered by priority, then due date."
sql 
SELECT t.*
FROM tasks t
WHERE t.project_id = $1
  AND t.deleted_at IS NULL
  AND t.status <> 'done'
ORDER BY
    CASE t.priority
        WHEN 'critical' THEN 1
        WHEN 'high'     THEN 2
        WHEN 'medium'   THEN 3
        ELSE 4
    END,
    t.due_date NULLS LAST;
  1. "Get all projects that a user is a member of."
sql 
SELECT p.*
FROM projects p
JOIN project_members pm
  ON pm.project_id = p.id
WHERE pm.user_id = $1
  AND p.is_archived = FALSE;
  1. "Fetch a task with its comments and assignee."
sql 
SELECT
    t.*,
    au.full_name AS assignee_name,
    au.email     AS assignee_email
FROM tasks t
LEFT JOIN users au
  ON au.id = t.assignee_id
WHERE t.id = $1;

For comments, you might issue a separate query:

sql 
SELECT c.*, u.full_name AS author_name
FROM comments c
JOIN users u ON u.id = c.author_id
WHERE c.task_id = $1
ORDER BY c.created_at ASC;

If these queries feel natural and do not require hacks like parsing strings, your design is on a good track.

Step 15: Prepare for Migrations

In a real project you will not design the perfect schema from day one. You will:

Design with migrations in mind:

Examples of safe changes:

sql 
-- Add a nullable column first
ALTER TABLE tasks ADD COLUMN estimate_hours INTEGER;
-- Later, when all rows are updated and the app uses it
ALTER TABLE tasks ALTER COLUMN estimate_hours SET NOT NULL;

Summary

When designing the database for your final project:

This data model will be the foundation for building the REST API and connecting PostgreSQL and other components in the next chapters.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

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