What is the best approach to database design?

Question

MySQL

Which approach is more advantageous in the future, for example, there are tables "contacts" and "users" . Let them have +/- the same set of fields. contacts are users who have not yet paid for the service and cannot authenticate on the site. After payment, the contact is converted into a user.

I think that this approach is not optimal and it is better to make some kind of flag in the "users" table of the "status_id" type and immediately add clients to this table. And when authenticating, just check email, password and status_id. But I don't know how this model will behave when the threshold of 100k+ users is reached.

The whole point in a nutshell: what is better, faster etc to have 2 almost identical tables or one, but use some kind of status_id (index, of course) for sampling? Assuming that there are hundreds of thousands of records in the database.

Answer 1

what is better, faster etc to have 2 almost identical tables or one, but use some status_id (index, of course) for selection

In this case, one is better.

Assuming that there are hundreds of thousands of records in the database.

for any modern DBMS, this is a very small amount of data

Answer 2

This is what you are looking for Zhamki

Answer 3

But I also knew, and forgot, Hoare's dictum that premature optimization is the root of all evil in programming

It is necessary to answer a few questions:
- Does the database have such a need (as optimization). Do you already have such traffic? If so, then you really should think about it
- You should also think about the domain (business model), if there is such a thing as contacts in business or it was invented by you. The cognitive load will increase if, for example, in your business it is called a potential customer (for example). Entering the database structure, without documentation, it will not be obvious to copy from the contacts to users table.
- The contacts table is data denormalization, a common practice to save a read operation, how justified here is not clear, since the task is taken out of the general context (any condition or future prospect can affect it). If you just need to distinguish between the state of user, then you can make a note in this table. If you have additional work with a candidate user (signing contracts, negotiating with saving data about it, and so on), that is, the process is not instantaneous from one state to another, then you probably need to store it in another table.
I don’t know what state your project is in, in my opinion, focusing on database performance is not a priority step at the very beginning.✌️