best practice for designing system with multiple user type

We have a system with two main roles: service provider and customer. The provider side is users like doctors, nurses, and caregivers. The customer side is just the customer. all user types contain some common data and some uncommon data. in the current system, we have a table for each user type, and for common data, we have User table. currect system ERD is:

https://s4.uupload.ir/files/screenshot-20210710165449-1007x662_tpwd.png

in the current system, we have a lot of tables and we think about reducing them. our vision is to bring all user types in a single table called User and instead of a lot of tables, we have more columns. of course in some users, we have empty cells that do not belong to this user type.

I have 4 questions:

  1. is it ok to bring customers and providers to a table like User?
  2. what is the optimal number of columns in a table?
  3. load a row with a lot of columns OR relation between different tables?
  4. provider type should be a separate table or can be an enum?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

  1. It is best to put all users in single table. So when you check login there is less place to do mistake. When selecting user you dont need to use SELECT * FROM… You can use SELECT id, username, name FROM…
  2. Dont put too many columns, if there is some data which you dont need when searching or displaying users, you can create helper table “user_meta” with dolumns user_id, meta_key, value where user_id and meta_key are primary key
  3. Answered by first 2 answers
  4. Provider type should be enum if there will not bee needs to expand with additional types.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x