I will give an example. There is a table of users of the site, each user has a login and password.
There is a table of buyers on the site, they are also users, but besides all user data they have, for example, email and balance.
There are sellers - these are users, but with login and password and phone fields.
There is a director - he has all user fields and 10 others.
There is a chief accountant - he has all the fields of the user, and 8 fields are the same as those of the director.
And so on...

So, I created a user table. Then he made the table buyers, added user id and unique fields for the buyer there. Also with the seller.
I also created another table, inserted the user id and all the fields of the remaining dudes.

On the one hand, everything seems logical. But I do not like it. Maybe there is some more beautiful solution?

Has anyone encountered such a problem?

    1 answer 1

    Create a separate table for each type of user and transfer there all the fields except those common to all types (for example, login and password). Then associate all specialized tables with the primary through a foreign key with a 1: 1 ratio.

    Then you will have the following set of tables:

    • users : id, login, password;
    • customers : user_id, email, balance;
    • sellers : user_id, phone;
    • ...

    To work with the main and auxiliary tables as a whole, without disturbing the normalization (“more beautiful solution”), you also need to create a unifying view. By the way, it is updatable, that is, it supports updating and deleting records.

    In SQL, it will look something like this:

    -- Таблица всех пользователей create table users( id int not null auto_increment, login varchar(15) not null, password_hash binary(20) not null, password_salt binary(10) not null, primary key(id) ); -- Дополнительная таблица пользователей-покупателей create table buyers( user_id int not null, email text not null, balance int not null, index user_id_key(user_id), foreign key buyers_users(user_id) references users(id) on delete cascade on update cascade ); -- Представление, объединяющее все данные о покупателе create view buyers_users_view as select id, login, password_hash, password_salt, email, balance from buyers left join users on buyers.user_id = users.id;

    The same code on SQLFiddle: http://sqlfiddle.com/#!9/582b8be .

    • I thought so initially. The problem was in the implementation of models that would work with two tables with one object. But this is another question - Dmitry Matvienko
    • one
      @ Dmitry Matvienko, added to the response information about the views (view). - ߊߚߤߘ