How Can I Efficiently Structure a Data Model for Handling Both One-Time and Recurring Tasks in a Task Management app?

fellow developers and architects! I'm in the process of designing a webapp aimed at managing tasks and recurring tasks, which can be seen as habits. The unique challenge I'm facing revolves around how to best structure my data model to efficiently handle both one-time tasks and recurring tasks, without differentiating between them during data retrieval. The current design splits the "task" concept into two entities: 1. **Task**: Holds shared task data such as title, description, category, type, recurrence rule, etc. 2. **State**: Contains specific instance data like state (new, in-progress, done), start date, due date, etc. This structure necessitates that both tables/entities be joined and fetched together at all times, considering that task entity attributes (like description and title) are often updated. **To provide more context, here are a few use cases to consider:** **Usecase1**: Imagine a calendar monthly view, and today is January 1st, with each user having hundreds of tasks, each with recurrences many times a week. The user wants to see all the tasks they have to do in the following December and change the due date/time of some of them. **Usecase2**: With the same setup as above, a user has a task with a daily recurrence. They now want to change the recurrence to every second day and change the due time. **Usecase3**: Again, the same calendar setup, but now the user wants to change the description and title of some tasks on a regular basis. Of course, this change should apply to every single recurrence of that particular task. **Given these requirements, I have several questions for the community:** 1. **Performance & Scalability**: What are your thoughts on the proposed data model in terms of performance and scalability? 2. **Database Selection**: Which type of database would be best suited for this application (SQL vs. NoSQL) and why? 3. **Alternative Models**: Are there more efficient data models or structures that could handle these scenarios better? 4. **State Entity Creation**: For recurring tasks, should "state" entities be generated in advance or on-demand? How can this be optimized for both the user experience and system performance? Considering the data model I described, how and when should the “state” entities be created for a task? In advance? On demand? If I create state entities in advance, I risk that the user picks a time frame where those states are still missing, so I have to make a check anyway if new state entities need to be created. On the other hand, if I do it on demand it will make all the queries very slow as with each fetch operation I first need to check and if needed to create the state entities. Your insights, especially if you've tackled similar challenges, would be invaluable to me. I'm particularly interested in any architectural advice, design patterns, or technology recommendations that could enhance the app's functionality and user experience.