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:
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:
- is it ok to bring customers and providers to a table like User?
- what is the optimal number of columns in a table?
- load a row with a lot of columns OR relation between different tables?
- provider type should be a separate table or can be an enum?
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…
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
Answered by first 2 answers
Provider type should be enum if there will not bee needs to expand with additional types.