Database design: 4 types of users but have different functionality , separate or one table?

Question

I have 4 types of users:

Admins , normal user , company , service provider

Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share some attributes too (id .company name ,phone ,fax ,mail ).

They interact too with other entities in application to access some feature like post job or event or apply for it

Is it better to put them all in one user table like tbl_users or is it better to create separate table to every one ? or add to two tables one for (Admins and normal user) and other for ( company and service provider)

Answer 1

A simple design you can go for is to choose three tables

Table MasterUser

UserId - PK - Identity(auto increment) 
phone
mail

Table AdminNormalUser

UserId -- Pk of this table and UserId of `MasterUser`
FirstName
LastName

Table CompanyPorviderUser

UserId -- Pk of this table and UserId of `MasterUser`
CompanyName
Fax 

This design will give you a unique Id to each user, no matter a Admin, Normal, Company or provider user, which is a requirement for application that will use these tables, And it will get rid of columns with NULL values.

Answer 2

If you keep the Separate tables, you may face issues with referential integrity if both type users are interacting with same data, but you dont want unnecessary nulls in your user table so what I would suggest is create a separate user attribute table for non common user attributes

so 1) tbl_user contains all the common columns with flag to specify type of user 2) tbl_AdminAttribute Contains related UserID + Extra Filed 3) tbl_CompanyAttribute Contains related UserID + Extra Filed 4) tbl_ServiceProviderAttribute Contains related UserID + Extra Filed

Hope that makes sense

Answer 3

In order to avoid using a large amount of joins, which could be a heavy load on a database server, store each user individually in a separate table.

The only problem you can face with this type of structure is with the evolution of the application. At some point you may want to store an extra bit of information about two particular types of users(i.e admin and normal users). If this happens, there will be several places where both the database and the application will need to change, which is prone to human error.

If you are worried about this type of issue, you could think about using a parent-child relationship or implementing some inheritance in your database.

Here are several techniques for this type of structure if that is what you choose to implement: https://stackoverflow.com/questions/386652/techniques-for-database-inheritance