How to build a database suitable for working with a large number of people?

Question

Database design

As part of the study of Java, I reached JDBC, or rather MySQL *. Using it, I'm making a task manager application. While there is only me, then everything is clear - one table will suffice. But what if there will be not 1, but, say, 100 or 10k people? How to fit all this data in one table? Or do you need to make a main table with all users and select a separate table for each, according to which you can already search for the data of the selected user?

The data is: INT-sequence number, VARCHAR-name, VARCHAR-description, VARCHAR-tags (categories), TINYINT-urgency, BOOLEAN-status, VARCHAR-list of steps, SMALLINT-progress, DATETIME-deadline.

I understand that the task of optimizing the database (and the entire application as a whole) is a matter of the future, but I want to either do it right right away, or at least so that later I can refer to the code of this program and remember how I did everything here, that everything worked fine, and take pieces of code to other projects.

If you have a good Java GitHub project in mind that uses JDBC to store user data for a large number of people, please post a link.

* I chose MySQL after looking at job openings for junior java. There most often either MySQL or PostgreSQL. I do not see much difference between them at this stage, so I chose at random.

Answer 1

How to fit all this data in one table? Or do you need to make a main table with all users and select a separate table for each, according to which you can already search for the data of the selected user?

One table for users and another for tasks.
Between them is the Foreign Key, which maps tasks and users.
10k is not enough, so you won't even notice drawdowns.
To make selections faster, you can add indexes.

VARCHAR tags (categories)

It makes sense to put the categories in a separate table and let there be a Many-Many relationship

TINYINT-urgency

But in postgres, you could do ENUM :)

BOOLEAN status

Why can a task have only two statuses? For extensibility, it's worth doing this as an enam (string or number)

VARCHAR list of steps

Steps should also be put in a separate table and Associated with the task

SMALLINT progress

Like the current step?