What is the best way to store multiple users with shared properties?

Question

MySQL

The essence of the problem:
There are 3 types of users, all 3 types have a set of properties, both common and unique for each, the 2nd and 3rd types also have properties that the first one does not have.
At the moment, there is an idea to form the base as follows (I will attach the diagram later, I am writing from the phone):

• user table, for all common properties, pk of each user to store in it
  
• table user_common_type_2_3, to store common properties for type 2 and type 3
  
• tables user_type_1, user_type_2, user_type_3
  

relationships between tables - 1:1
But such an idea is alarming - tomorrow a user with type 4, 5, 6 ... may appear, which has common properties with 1m, 3m, etc.
Second question:
What is the best way to implement this in the application.
Of course, you need to create a base class, then inherit it. In each class, have a set of properties defined for a particular user and work with this interface further.
The implementation with your own writing of queries does not cause problems, but how to implement it using the ORM is not entirely clear. An implementation using Active Record and Doctrine would be interesting (on Symfony I solved a similar problem earlier, but there were only two types of users and an implementation without using Doctrine).
Thank you very much in advance.

Answer 1

Property sets are, in fact, roles. Permissions are given to roles, and users can have multiple roles. When checking the user's right to act, you need to check the rights of each of his roles. Very strange question since Yii already has RBAC .

Answer 2

as an option, you can look towards NoSQL solutions

Answer 3

The general - in one table, a miscellaneous - in separate. If user_type_2 and user_type_3 are the same in terms of business logic - in one, otherwise in different ones. The requirements are likely to change and more than once it is cheaper and easier to play it safe.

Answer 4

It is possible to store all the data in one table with the "type" field, where the description of what kind of user it will be will be stored. Somehow so . Thus, you will get rid of the 1 to 1 connection. You can do it with 1 to 1 as you yourself wrote. If you work with "symphony", then the "doctrine" supports both of these options out of the box, see here .