Which one of these two contact management database designs is correct or best practice?

Question

In this first database design, column name starting from first_name to country exist in each table. The relationship of each table to table user_categories is ONE TO MANY.

In this second database design, there is no duplication of column name in each table. The relationship of table users and table shippers, suppliers, employees and customers are ONE TO ONE. Is the relationship correct?

The purpose of table shippers, suppliers, employees and customers is to store users based on its category in respective table just like the first design. I'm curious if this is a good practice since the columns of fourth tables is only its ID. Is there any column that I can add to those tables?

Could you please advice me if there are any better solutions?

Answer 1

In general. Recommended structure (assuming one user can fit into several categories):

users (user_id, 
       name, 
       -- etc., 
       primary key (user_id))

categories (cat_id, 
            category_type, -- shipper, supplier, etc.
            name, 
            -- etc., 
            primary key (cat_id))

users_to_categories (user_id, 
                     cat_id,  
                     -- etc.,
                     primary key (user_id, cat_id),  
                     foreign key (user_id) references users (user_id),  
                     foreign key (cat_id) references categories (cat_id))

If some user fits in more than one category there is more than one record in the last table with the same user_id and different cat_id.

A user can only choose one category for example, supplier. If he wants to change to customer, he must delete his account first.

If so:

users (user_id, 
       cat_id,
       name, 
       -- etc., 
       primary key (user_id),
       foreign key (cat_id) references categories (cat_id))

categories (cat_id, 
            category_type, -- shipper, supplier, etc., ENUM
            name, 
            -- etc., 
            primary key (cat_id))