Project Management ER diagram and database schema

What this schema models

A complete project management and issue tracker database design, built the way modern trackers actually work: 26 tables, 14 enums and 57 relationships covering workspaces and members, projects with their own keys and workflows, issues with types, priorities, parents and links, sprints, milestones and saved views, comments, attachments, watchers and notifications, an append-only activity log, time tracking and typed custom fields. THE FOUR DESIGN RULES 1. Workflow states are DATA, not an enum. Each project defines its own columns (Todo, In Review, QA, Shipped), and every state maps to one of five fixed categories: backlog, unstarted, started, completed, canceled. Teams rename and reorder without a migration, and reports still know what "done" means in every project. 2. Issue keys come from a counter row. issue_counters holds one row per project; creating an issue increments it with UPDATE ... RETURNING in the same transaction, so WEB-123 never collides, a rolled-back insert leaves no gap, and nobody runs MAX(number) + 1. The key is never stored as text: it is the project prefix plus the number. 3. Order is a rank string, not an integer. Issues carry a lexicographic rank (a fractional index), so dragging a card between two others writes one row, a new rank that sorts between its neighbors. Nothing below the drop point is renumbered. 4. History is append-only. issue_activity records every change (field, old value, new value, actor) and is never updated or deleted. It is the audit trail, the activity feed, and the source for cycle time, lead time and cumulative flow charts. DETAILS WORTH STEALING - Moving an issue to another project gives it a new key, and issue_key_aliases keeps the old key resolving, so links in old chats and commit messages still work. - Issue links are stored once and read both ways: "A blocks B" is one row, and B shows "is blocked by A" from that same row. - Custom field values use typed columns (text, number, date, user, option) instead of one text blob, so numbers sort as numbers and select options are real foreign keys. Renaming an option rewrites nothing. - Unwatching mutes the watcher row instead of deleting it, so the next mention does not quietly subscribe someone again. - Time is logged in whole minutes against the day the work was done, kept apart from when it was entered, so timesheets add up exactly. - A partial unique index allows only one active sprint per project, and each sprint snapshots its committed points at start so scope added mid-sprint is visible. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: workspace and people, projects and workflow, planning, issues, collaboration and history, and time tracking and custom fields. Every table carries a note. Postgres types throughout; the three jsonb columns (saved filters and the activity log's old and new values) map to JSON in MySQL or NVARCHAR(MAX) in SQL Server. Useful for developers building an issue tracker, kanban board or agile planning tool, and for students who need a realistic project management system ER diagram for a DBMS project. This is a schema design reference; permissions, automations and integrations belong in your own application logic.

Tables (26)

Relationships (57)

• attachments.comment_id references comments.id many to one
• attachments.issue_id references issues.id many to one
• attachments.uploaded_by references users.id many to one
• comments.author_id references users.id many to one
• comments.issue_id references issues.id many to one
• comments.parent_comment_id references comments.id many to one
• custom_field_definitions.project_id references projects.id many to one
• custom_field_definitions.workspace_id references workspaces.id many to one
• custom_field_options.field_id references custom_field_definitions.id many to one
• custom_field_values.field_id references custom_field_definitions.id many to one
• custom_field_values.issue_id references issues.id many to one
• custom_field_values.value_option_id references custom_field_options.id many to one
• custom_field_values.value_user_id references users.id many to one
• issue_activity.actor_id references users.id many to one
• issue_activity.issue_id references issues.id many to one
• issue_assignees.assigned_by references users.id many to one
• issue_assignees.issue_id references issues.id many to one
• issue_assignees.user_id references users.id many to one
• issue_key_aliases.issue_id references issues.id many to one
• issue_key_aliases.project_id references projects.id many to one
• issue_labels.issue_id references issues.id many to one
• issue_labels.label_id references labels.id many to one
• issue_links.created_by references users.id many to one
• issue_links.source_issue_id references issues.id many to one
• issue_links.target_issue_id references issues.id many to one
• issue_watchers.issue_id references issues.id many to one
• issue_watchers.user_id references users.id many to one
• issues.milestone_id references milestones.id many to one
• issues.parent_id references issues.id many to one
• issues.project_id references projects.id many to one
• issues.reporter_id references users.id many to one
• issues.sprint_id references sprints.id many to one
• issues.state_id references workflow_states.id many to one
• labels.project_id references projects.id many to one
• labels.workspace_id references workspaces.id many to one
• milestones.project_id references projects.id many to one
• notifications.activity_id references issue_activity.id many to one
• notifications.issue_id references issues.id many to one
• notifications.user_id references users.id many to one
• project_members.project_id references projects.id many to one
• project_members.user_id references users.id many to one
• projects.default_state_id references workflow_states.id many to one
• projects.id references issue_counters.project_id one to one
• projects.lead_user_id references users.id many to one
• projects.workspace_id references workspaces.id many to one
• saved_views.owner_id references users.id many to one
• saved_views.project_id references projects.id many to one
• saved_views.workspace_id references workspaces.id many to one
• sprints.project_id references projects.id many to one
• time_logs.issue_id references issues.id many to one
• time_logs.user_id references users.id many to one
• workflow_states.project_id references projects.id many to one
• workflow_transitions.from_state_id references workflow_states.id many to one
• workflow_transitions.to_state_id references workflow_states.id many to one
• workspace_members.invited_by references users.id many to one
• workspace_members.user_id references users.id many to one
• workspace_members.workspace_id references workspaces.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Project Management database schema?

26 tables and 57 relationships, across 179 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.