How to normalize a database where different user groups have different kinds of profiles? -

In my application database there is a group table that separates users into logical roles and defines the level of access ( Administrator, owner, vendor, customer service, etc.)

There are several users of the group, including login details like usernames and passwords in the user table.

Now I want to add user profiles to my database. The problem I have (probably due to my relative unfamiliarity with proper database generalization) is that different user groups have different types of profiles, a vendor The profile will include its commission percentage, whereas an administrator or customer service will not need this value.

So, will it be the proper way to create a unique profile table for each group? (Such as admin_profiles, or salesperson_profiles). Or there is a better way that adds some details to a normal profile, while some users have increased the information. And if so, then what is the example of how it is done with the example of the commission?

A case where these profiles are actually defined by real world norms, For the purpose of full disclosure, however, you can use the EAV system to store these. Something similar:

  User: UserID ... ProfileId Profile: ProfileID Name Criteria: Standards Name ProfileCertificate: ProfileId Criteria User Security: UserID Benifits  

The profile table defines the basic table for the profile for each of your profile types it will contain a row.

Criterion defines the basic parameters on a profile, regardless of how the profile is (for example, you may have the same criteria on more than one profile).

CriteriaProfile works to create: profile and criteria . This is where you add things like sorting for criteria within a particular profile.

User Catratas indicates the user's specific values ​​for a given criteria, this will allow you to switch profiles for a user and maintain any criteria. Which is done only by removing those people who were not part of the new profile.

However

Come with very high overhead to maintain EAV structures. Rather than relying on the facilities of the structured data of RDBMS (which is given to people to pay a lot of money), you now have to manage yourself Tables are very much on the large scale as compared to non-EAV systems. Grow fast, because you have a number of rows multiple times (as you now have a line for every column in a normal structure).

EAV is powerful and flexible, but not always solution. If your profile needs to be dynamic, they can be a suitable device.