0

After reading Database design: 3 types of users, separate or one table? I am confuse on how I should design the database.

---------------------- 1st Design ----------------------

USERS

  • username (PK)
  • password
  • account type (Admin, Manager or Worker)

PRO: When log in I can easily retrieve if this user is Admin, Manager, Worker

CON: I cannot form relationship (ONE Manager to MANY Workers) using this design.

---------------------- 2nd Design ----------------------

USERS

  • username (PK)
  • password

ADMINS

  • username (PK and FK to USERS.username)
  • password

MANAGERS

  • username (PK and FK to USERS.username)
  • password

WORKERS

  • username (PK and FK to USERS.username)
  • owner (FK to MANAGERS.username)
  • password

PRO: Relationship can be formed

CON:

  • When log in I have to find if user is Admin/Manager/Worker by looking in each of the table.
  • It is also hard to enforce if one username appear multiple time across ADMINS, MANAGERS and WORKERS (BIG CONCERN!!!)

I have been spending several hours searching google for good design but couldn't find any solution. I am sure this kind of requirement is quite common? Please provide some insight! Thank you!

Community
  • 1
  • 1
Zanko
  • 4,298
  • 4
  • 31
  • 54

3 Answers3

2

You can form a type of one-many relationship with the first design - just add a ManagerID that refers back to the UserID.

You end up with something like this:

UserID    Username    AcctType    ManagerID
1         Bob         Manager     NULL
2         John        Worker      1

Another option is to add a third table that does the linking between a worker and a manager.

User table:

UserID    Username    AcctType
1         Bob         Manager
2         John        Worker
3         Mark        Worker

UserLink table:

ManagerID    UserID
1            2
1            3
  • Hi, for user link table, the data integrity might happen if there are such data (1, 2) (1,3) (2, 3) ... Worker to Worker? Another problem might be one worker might have many managers. If there is a way to enforce this let me know! Without the link table, how do we ensure that Manager/Admin will behaves as expected? That is no Manager ID of random value – Zanko Jan 10 '17 at 04:14
  • Maybe we can enforce ManagerID to be NULL if AccType is Admin/Manager? – Zanko Jan 10 '17 at 04:14
  • The UserLink table would allow for workers to have multiple managers, and even for the manager to have a manager. Both fields, ManagerID and UserID, would be a foreign key that links to the User table, so they can't have garbage data. –  Jan 10 '17 at 04:17
  • Thank you for response! What if I need to enforce, one manager to many workers strictly? – Zanko Jan 10 '17 at 04:19
  • That would be a business rule you'd have to enforce in the application. –  Jan 10 '17 at 04:20
  • I see... I always read "The database is responsible for data integrity". Anyway there is absolutely no way to enforce this requiremnt? How about using the first approach you said and enforcing admin/manager to have NULL for manager_id – Zanko Jan 10 '17 at 04:22
  • The first approach would allow a manager to have many workers, but not for a worker to have many managers. The second way is more flexible, but with that flexibility you have to do some extra management of the data. –  Jan 10 '17 at 04:30
  • Thank you so much for fast response! I think I'll go with the first approach! – Zanko Jan 10 '17 at 04:32
  • Hi one more question. For UserLink Table. How do we maintain data integrity regarding ManagerID is reference to manager and not other user with (Admin/Worker) role? – Zanko Jan 10 '17 at 04:50
  • For me, I would enforce that in the application as a business rule. Business rules can change, and that's usually the best place to manage them. What happens when a manager changes roles (i.e. gets a promotion)? You'll have to manage those types of issues in the application.. –  Jan 10 '17 at 04:54
1

The golden design will be :

users (id, username, pass, ...)
roles (id, role, ...)
role_users(id, role_id, user_id, ...)
SIDU
  • 2,258
  • 1
  • 12
  • 23
0

Another way:

 users:
   id             serial primary key
   name           text
   password       text
   role           roletype    
   manager        int
   managerrole    roletype

and some constraints.

foreign key (manager,managerrole) references users (id,role)

check ( case when role = 'worker' then managerrole is not distinct from 'manager' and manager is not null else managerrole is null and manager is null end )

now the first constraint forces managers to be present in the user table, and the second constraint enforces that only user with role workers can have a manager and leverages the first contraint to enforce that all such managers have the role 'manager'.

I think you also need a unique index on (id, role) and an ordinary index on (manager,managerrole)

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • Hi thank you for the input! Why do we need additional managerrole field? Can't we have only manager reference to be value or NULL if it is admin/manager? – Zanko Jan 10 '17 at 04:36
  • it's there so that the foreign key constraint can make sure that the id pointed to by the manager column has the apropriate role. – Jasen Jan 10 '17 at 04:42
  • I see your point now! CHECK ( CASE WHEN role != 'Worker' THEN manager IS NULL END) won't work because I can't seem to check when role is worker that manager is valid manager. This question is so tricky and no straight forward solution : ( Solution feel so "hackish" haha – Zanko Jan 10 '17 at 04:45
  • yes, if postgres would allow constants in foreign key constraints it could be tidier. – Jasen Jan 10 '17 at 05:16