Main Entities
While designing the database through an ERD, I found that the main entities are users, boards (that represent projects), columns (that represent stages) and tasks.
I designed the system's data model to be user-owned data, so every user is admin of the boards that are created by them, and there is no admin role that give full control on all the data.
Database Relational Schema
Roles
I decided to have four roles: Owner, Manager, Assignee and Viewer:
- Owner: is given automatically to the user that created the board/project, allowing them to perform project-level CRUD operations in addition to CRUD operations against the columns.
- Manager: allows performing task-level CRUD operations.
- Assignee: can only change the status of a task (by transferring it from a column to another)
- Viewer: can not take any action, can only view the board details (giving the access to the project data).
We need another table to store the role of each user per board/project, so user_board_role table is the only place where the permissions/roles are saved.
So, totally we need 5 tables: users, boards, columns, tasks and user_board_role
Also we need a table to store the events that happens, but this will be added when we setup the event-driven architecture for the system.
Handling the Order of Tasks in a Column
In the tasks table we need to preserve the order of the tasks, so when retrieving the tasks for a specific column, we need to sort them by their order.
Possible Inefficient Solution
A naive solution for this is to have a column inside the tasks table that stores a number representing the order of the task (index of the task).
However, this solution is inefficient as when we need to change the order of a specific task, we need to update all the tasks between its old position and new position and increase the number that represents their order by 1.
The time complexity of this algorithm is O(n), as for each task we need to change we have to loop over all the tasks between its new and old positions and update them.
The Chosen Efficient Solution
The more efficient approach is to store the order as a decimal (fractional index). So when we need to change the position of a specific task, all we need is to find a decimal between the index of the previous and next task to be the new index of the transferred task. We sort the tasks according to their index to get a sorted list of tasks.
For example, task A has index 1, task B has index 2, dropping a new task between them requires calculating a new index for the new task that is 1.5.
Its time complexity is only O(1), so it is very efficient. However, after many operations we may face precision problems, the solution is to rebalance when two adjacent indices get too close in value to fit a new decimal between them. Rebalancing is just looping over the tasks and assigning them integer-valued indices.
Top comments (0)