patternMinor
Database design: 4 types of users but have different functionality , separate or one table?
Viewed 0 times
tabledesignbutseparatedatabasedifferentonetypesusersfunctionality
Problem
I have 4 types of users:
Admins and normal user share some attributes (
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)
Admins , normal user , company , service providerAdmins 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)
Solution
A simple design you can go for is to choose three tables
Table
Table
Table
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
Table
MasterUserUserId - PK - Identity(auto increment)
phone
mailTable
AdminNormalUserUserId -- Pk of this table and UserId of `MasterUser`
FirstName
LastNameTable
CompanyPorviderUserUserId -- Pk of this table and UserId of `MasterUser`
CompanyName
FaxThis 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.Code Snippets
UserId - PK - Identity(auto increment)
phone
mailUserId -- Pk of this table and UserId of `MasterUser`
FirstName
LastNameUserId -- Pk of this table and UserId of `MasterUser`
CompanyName
FaxContext
StackExchange Database Administrators Q#81295, answer score: 3
Revisions (0)
No revisions yet.