Currently, I have a requirement requiring me to develop a to-do list structure that includes projects, tasks, and subtasks (in hierarchical order) and each of these tasks can have a different set of users who may engage in separate conversations, similar to the comment structure we have in Google Sheets. What would be a good database schema - should I have projects, tasks, and subtasks as separate collections, or should I have them separated by type as a single collection?
I think there is no single answer to this,
- You should consider how much those projects,tasks and subtasks are relate to each other.
- if they have no relation and your data fetching is based on type difference, difference collection can be more clear and easily managable.
- or if you want to fetch data based on user activity, single collection can provide a single query to the database.
This is just factors (I just think of now) for you to consider and you should decide base on your needs.
Thanks for your response. Here is a bit more detail about my use case. Projects contain tasks, and those tasks contain subtasks, and each of them has user activity so if one user checks one subtask, then the other user should be able to see that subtask as completed in real-time. This can occur at every level, including the project, the task, and the subtask. It is also possible at each level to have a full notes section that would function similarly to comments, allowing each user to leave a comment and receive a response.