How to design a database of multi-user lists?

Question

MySQL

Imagine that you need to create a multi-user service with task lists, that is, each user can register and create their own task lists. So I want to ask for such purposes to create 1 table with all the goals and just make a selection by user, but then such a table will grow very much and there will be a lot of requests to it (in general, how critical is this and what table sizes are already considered not optimal?) or the second option is to make a table for each user, but then there will be many identical tables and it will be difficult to change everything at once (for example, add a new column).

Answer 1

Forget about the second option, it's some kind of hellish anti-pattern. As for the first option - don't worry about the size, indexes will save you. But when they stop saving you, you will use sharding ... but by this moment you will know more about big data than anyone else in this discussion)) I would create a table with users, a table with tasks and a table of correspondence between users and tasks with a composite primary key on these two columns. And don't forget about foreign keys. I would do indexes in both tables of the auto-incrementing bigint type (I don’t know in terms of mysql) for more compact and faster indexes compared to guid. If it were postgresql, then it would be possible to make the translation table look like (user, array of tasks), but this is bad practice in terms of data consistency.

Answer 2

what table sizes are already considered suboptimal

Let's say several billion
Read better textbooks on RDBMS.

Answer 3

1 tablet + partitioning and sharding