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.
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
I’d keep them all in one table, and add a field to indicate what type of user it is. You’ll have some blank fields (for example admins and normal users don’t have fax numbers) but this shouldn’t be an issue unless you have lots and lots of users and some fields are only used for a very small proportion of them. You could also fill in the blanks since there’s no reason why normal users can’t have a fax number, or why service providers can’t have first and last names.
If necessary you could have a “users” table and a “user attributes” table but it’s probably not worth it for a small number (i.e. a few hundred) users.
1
Unless the different user types really have different roles in the schema, keep them in one table. I would have fields such as the following:
- User type (user, admin, company, provider)
- First and last name
- Company name
It may even make sense to separate the phone numbers and addresses into other tables, to allow one user to have many phone numbers and addresses (perhaps a company has multiple offices, a user has home, cell, work numbers, etc) although this is technically not part of the question.
The important thing to remember is that a user type is easily represented as a field on a table, allowing other tables to point to any user: this enforces a single relationship type. Furthermore, users can change types without needing to map data between tables, update foreign keys, etc. What if a normal user becomes an admin? What if he is working out of his home and incorporates, maybe hires an additional worker? It is quite feasible for users to change types in the real world, your business rules may be different of course.