How to organize tables in a database?

Question

MySQL

Task: user with fields: first name, last name, email, password; user type: teacher, administrator and additional fields: teacher - phone, administrator - city, street, house. How to organize tables? One table of users and additional fields to do with the possibility of null?

Answer 1

There are several basic implementation options:
1. as you indicated: one table, unused fields to make null. This is the Single Table Inheritance pattern. Works well if there are few types and additional fields.
2. one table for general properties (name, surname, email, password), the second for teachers, the third for administrators. The second and third have the fields they need. This is the Multiple/Class Table Inheritance pattern. A fairly flexible option, but you will need to use join.
Here are some examples https://romaricdrigon.github.io/2019/06/11/doctrin...

Answer 2

I will supplement and correct Dmitry 's answer , with the first option everything is clear and so, the only clarification is that this is not as flexible as possible and is considered bad practice in real projects. If tomorrow you need to add new entities to this structure, the whole structure will go down the drain and you will have to rebuild everything.
With the second option, everything is more or less good, the only thing that is not mentioned is that the user type must also be stored in the base table, and join with such a structure makes no sense, since in any case you will have to perform 2 requests - one to log in, the other to get data from the desired table, based on the type of user, well, or bother with fairly complex queries based on ifs, etc., which becomes even worse with the addition of additional entities. Ideally, there should still be a reference table for user types, but these are already nuances.
Well, there is a 3rd option, which in this SPECIFIC case is less suitable, but generally more suitable for the meaning - a common table of entities with basic parameters + a set of attributes for subgroups of entities. It's called EAV .