There is such a scheme:

  • The system has many users
  • Each user has more than one to-do list
  • Each list, of course, has "cases" that need to be done, and they, in turn, can be marked as done

I have ideas for now

1 table:

[User] id, name, pass...

2 table:

 [List] id(int), user_id(int), name(varchar), description(varchar)

3 table:

 [Task] id(int), list_id(int), text(varchar), done(bool)

QUESTION:
How to simplify the data schema so that later you don’t have to worry about writing logic?

  • Are the [Task] tasks typical or something new every time? - user3195373
  • five
    Leave as is. As an option - to add three fields with time: when the task is added, when the deadline and when it is solved. Then the done field can be removed. - Denis Khvorostin
  • 2
    Yes, the scheme looks fine. - DisguisePerceptron
  • It seems that the scheme should be complicated, and not simplified. The scheduled time to complete the task. Actual time. Last modified dates. Surely the task may have subtasks or just a dependency. It also seems very important priority tasks. This is not to mention the comments and notes) - Viktorov

1 answer 1

If you strictly decide to use the relational model, then it seems to be normal. But imagine, if you suddenly need to get access to the "case" in some list, and you only have user_id, then you will need to make 2 queries or join, which can begin to strain your base over time.
Therefore, I would use here a partially non-relational model and some nosql base. I would do it like this:

  • users would be stored in a separate table, as you have already written
  • lists and cases would be stored as tuples [user_id, todo_list_id, {json}] in non-relational database
  • one list is one tuple
  • indexes must be on user_id and on user_id + todo_list_id
  • they themselves would immediately arrange in the form of Jason, so that it would be convenient to expand / change
  • On the other hand, if there can be a lot of cases “infinitely”, then such a scheme also “does not shine in the forehead”. But you can still think in this direction. - o2gy