Спроектировать модель данных для системы управления задачами

Решение: ER-диаграмма системы управления задачами

Обзор архитектуры модели данных

Система управления задачами требует многоуровневой иерархии сущностей с поддержкой совместной работы. Ключевая особенность — назначение нескольких исполнителей на задачу и категоризация через метки (tags), что требует использования отношений N:M.

Описание сущностей и их атрибуты

1. Users (Пользователи)

PK: user_id (UUID)
Атрибуты:
- user_id: UUID (первичный ключ)
- username: VARCHAR(255) UNIQUE NOT NULL
- email: VARCHAR(255) UNIQUE NOT NULL
- password_hash: VARCHAR(255) NOT NULL
- full_name: VARCHAR(255)
- avatar_url: TEXT
- is_active: BOOLEAN DEFAULT true
- created_at: TIMESTAMP WITH TIMEZONE
- updated_at: TIMESTAMP WITH TIMEZONE

2. Projects (Проекты)

PK: project_id (UUID)
FK: owner_id → users(user_id)
Атрибуты:
- project_id: UUID (первичный ключ)
- owner_id: UUID (внешний ключ, NOT NULL)
- name: VARCHAR(255) NOT NULL
- description: TEXT
- is_archived: BOOLEAN DEFAULT false
- created_at: TIMESTAMP WITH TIMEZONE
- updated_at: TIMESTAMP WITH TIMEZONE

Примечание: owner_id определяет пользователя, создавшего проект.

3. Boards (Доски)

PK: board_id (UUID)
FK: project_id → projects(project_id)
Атрибуты:
- board_id: UUID (первичный ключ)
- project_id: UUID (внешний ключ, NOT NULL)
- name: VARCHAR(255) NOT NULL
- description: TEXT
- position: INTEGER (для сортировки досок внутри проекта)
- is_archived: BOOLEAN DEFAULT false
- created_at: TIMESTAMP WITH TIMEZONE

Примечание: Связь 1:N с Projects.

4. Columns (Колонки)

PK: column_id (UUID)
FK: board_id → boards(board_id)
Атрибуты:
- column_id: UUID (первичный ключ)
- board_id: UUID (внешний ключ, NOT NULL)
- name: VARCHAR(255) NOT NULL (например: To Do, In Progress, Done)
- position: INTEGER (для порядка колонок)
- color: VARCHAR(7) (hex-код цвета)
- created_at: TIMESTAMP WITH TIMEZONE

Примечание: Связь 1:N с Boards.

5. Tasks (Задачи)

PK: task_id (UUID)
FK: column_id → columns(column_id)
FK: created_by_id → users(user_id)
Атрибуты:
- task_id: UUID (первичный ключ)
- column_id: UUID (внешний ключ, NOT NULL)
- created_by_id: UUID (внешний ключ, NOT NULL)
- title: VARCHAR(255) NOT NULL
- description: TEXT
- priority: ENUM (low, medium, high, critical)
- position: INTEGER (для порядка в колонке)
- due_date: DATE
- estimated_hours: DECIMAL(5,2)
- is_completed: BOOLEAN DEFAULT false
- created_at: TIMESTAMP WITH TIMEZONE
- updated_at: TIMESTAMP WITH TIMEZONE

Примечание: Связь 1:N с Columns. Поле created_by_id указывает создателя.

6. TaskAssignees (Назначенные исполнители) — N:M связь

PK: assignment_id (UUID)
FK: task_id → tasks(task_id)
FK: assignee_id → users(user_id)
Атрибуты:
- assignment_id: UUID (первичный ключ)
- task_id: UUID (внешний ключ, NOT NULL)
- assignee_id: UUID (внешний ключ, NOT NULL)
- assigned_at: TIMESTAMP WITH TIMEZONE
- assigned_by_id: UUID (кто назначил)
Композитный уникальный индекс: (task_id, assignee_id)

Примечание: Таблица связи для реализации N:M между Tasks и Users. Один пользователь может быть назначен на несколько задач, одна задача может быть назначена нескольким пользователям.

7. Comments (Комментарии)

PK: comment_id (UUID)
FK: task_id → tasks(task_id)
FK: author_id → users(user_id)
Атрибуты:
- comment_id: UUID (первичный ключ)
- task_id: UUID (внешний ключ, NOT NULL)
- author_id: UUID (внешний ключ, NOT NULL)
- content: TEXT NOT NULL
- created_at: TIMESTAMP WITH TIMEZONE
- updated_at: TIMESTAMP WITH TIMEZONE
- is_deleted: BOOLEAN DEFAULT false

Примечание: Связь 1:N с Tasks.

8. Tags (Метки)

PK: tag_id (UUID)
FK: project_id → projects(project_id)
Атрибуты:
- tag_id: UUID (первичный ключ)
- project_id: UUID (внешний ключ, NOT NULL)
- name: VARCHAR(100) NOT NULL
- color: VARCHAR(7) (hex-код)
Композитный уникальный индекс: (project_id, name)

Примечание: Теги привязаны к проекту, чтобы избежать дублирования.

9. TaskTags (Связь задач и меток) — N:M связь

PK: task_tag_id (UUID)
FK: task_id → tasks(task_id)
FK: tag_id → tags(tag_id)
Атрибуты:
- task_tag_id: UUID (первичный ключ)
- task_id: UUID (внешний ключ, NOT NULL)
- tag_id: UUID (внешний ключ, NOT NULL)
Композитный уникальный индекс: (task_id, tag_id)

Примечание: N:M связь между Tasks и Tags.

10. Attachments (Вложения)

PK: attachment_id (UUID)
FK: task_id → tasks(task_id)
FK: uploaded_by_id → users(user_id)
Атрибуты:
- attachment_id: UUID (первичный ключ)
- task_id: UUID (внешний ключ, NOT NULL)
- uploaded_by_id: UUID (внешний ключ, NOT NULL)
- file_name: VARCHAR(255) NOT NULL
- file_size: BIGINT (в байтах)
- file_url: TEXT NOT NULL
- mime_type: VARCHAR(100)
- uploaded_at: TIMESTAMP WITH TIMEZONE

Примечание: Связь 1:N с Tasks.

Диаграмма связей между сущностями

Users
  ├─ 1:N → Projects (owner_id)
  ├─ 1:N → Comments (author_id)
  ├─ 1:N → Tasks (created_by_id)
  ├─ N:M → Tasks (через TaskAssignees)
  └─ 1:N → Attachments (uploaded_by_id)

Projects
  ├─ 1:N → Boards
  ├─ 1:N → Tags
  └─ (косвенно к Tasks через Boards → Columns → Tasks)

Boards
  └─ 1:N → Columns

Columns
  └─ 1:N → Tasks

Tasks
  ├─ 1:N → Comments
  ├─ N:M → Users (через TaskAssignees)
  ├─ N:M → Tags (через TaskTags)
  └─ 1:N → Attachments

Tags
  └─ N:M → Tasks (через TaskTags)

Типы связей

• 1:1 — не использованы в базовой модели
• 1:N — Projects → Boards, Boards → Columns, Columns → Tasks, Tasks → Comments, Tasks → Attachments, Users → Projects
• N:M — Users ↔ Tasks (через TaskAssignees), Tasks ↔ Tags (через TaskTags)

Ключевые решения архитектуры

1. TaskAssignees — отдельная таблица для реализации N:M связи, что позволяет назначать несколько исполнителей на одну задачу

2. Tags — привязаны к проекту, избегая глобального дублирования меток

3. Audit Trail — поля created_at, updated_at, assigned_by_id обеспечивают отслеживание истории изменений

4. Soft Delete — поле is_deleted для комментариев позволяет восстанавливать удалённые комментарии

5. Indexing — рекомендуется создать индексы по внешним ключам и часто используемым полям (board_id, column_id, task_id, user_id)