sql – Database architecture / structure


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:

id, name, pass...

2 table:

id(int), user_id(int), name(varchar), description(varchar)

3 table:

id(int), list_id(int), text(varchar), done(bool)

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


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