sql – Database architecture / structure

Question:

There is such a scheme:

  • The system has many users
  • Each user has more than one "TODO" to-do list
  • Each list, of course, has "things" to be done, and these, in turn, can be marked as done

I still have such ideas

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 you don't have to worry about writing logic later?

Answer:

If you strictly decided to use the relational model, then it’s like norms. But imagine, if you suddenly need to get access to a "case" in some list, and you only have a 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 kind of nosql base. I would do it something like this:

  • users would be stored in a separate plate, as you already have written
  • lists and to-do's would be stored as tuples [user_id, todo_list_id, {json}] in a non-relational database
  • one list is one tuple
  • indexes must be by user_id and by user_id + todo_list_id
  • I would immediately arrange the cases themselves in the form of Jason, so that it would be convenient to expand / change
Scroll to Top