Accepted (November 2024)
The Gantt chart requires tasks to be displayed in a specific order that supports:
Initially, we considered:
Task tableTaskOrdering tableUse a database VIEW (Task_Ordering) that joins Task with computed ordering fields. The Task.display_order column is the source of truth.
Task Table (Source of Truth):
CREATE TABLE Task (
task_id INT PRIMARY KEY,
task_name VARCHAR(255),
project_id INT,
parent_task_id INT,
display_order INT, -- Ordering within siblings
planned_start DATE,
planned_end DATE,
...
);
Task_Ordering VIEW:
CREATE VIEW Task_Ordering AS
SELECT
t.task_id,
t.task_name,
t.display_order AS task_order_id, -- For Gantt UI
t.parent_task_id,
...
FROM Task t
WHERE t.deleted = 0
ORDER BY t.display_order;
| Rule | Reason |
|---|---|
| Never INSERT into Task_Ordering | It's a VIEW, not a table |
| Update Task.display_order | This is the source of truth |
| Always set display_order | Use MAX+1 when creating tasks |
| display_order must not be NULL | Breaks ordering |
Reading (Gantt display):
# Query the VIEW for display
tasks = db.session.query(TaskOrdering).filter_by(project_id=project_id).all()
Writing (Reorder):
# Update the TABLE, not the VIEW
task = Task.query.get(task_id)
task.display_order = new_position
db.session.commit()
Maintain a separate table with ordering information.
Rejected because:
Use a materialized view instead of regular view.
Rejected because:
Add computed task_order_id column to Task table.
Rejected because: