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

data structuresdatabasedatabase-designdiagrams

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 )

and 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)

and this is some details about the entity attributes.

enter image description here

Best Answer

What you expose depends highly on your application needs.

One typically do dissociate users from contact informations allowing maximum flexibility. I'd recommend also to factorize the contact informations data into a single table.

User (1,1) --- (0,1) Contact_Information

Company (1,1) --- (0,1) Contact_Information

Service Provider (1,1) --- (0,1) Contact_Information

The user table would include login data and an addition is_admin field. The existence of a link between two entities would also let you the flexibility such a "real person" can represent both a normal user and a company.

I'd also recommend you to read about how to model inheritance in a database -> https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database

Related Solutions

Database – How to show levels of User Roles in ERD

You said it yourself, and gave it a good thought. Entity Relation Diagram. You have only one Entity of USER, thus you only need to put it once. Role is an attribute of that Entity.

I would digg deeper in the levels of roles in a Functional Diagram, where the Entities are used in a certain Functionality.

Database ERD design: 2 types user in one table

That's what I'd do:

table user (
  id primary key,
  name,
  email,
  -- etc
);

table approver (
  id primary key foreign key user(id),
  appointed_date, 
  -- etc
);

table appointment (
  -- whatever PK
  created_by foreign key user(id), 
  approved_by foreign key approver(id),
  -- etc
);

Now any user can create an appointment, but only users also present in approver table can approve it.

Best Answer

Related Solutions

Database – How to show levels of User Roles in ERD

Database ERD design: 2 types user in one table

Related Topic