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

Question

My application database has a group table that divides users into logical roles and defines access levels (admin, owner, salesperson, Customer service, etc.).

There are many users in groups. The Users table contains login information, such as username and password.

Now I want to add user profiles to my database. The trouble I am experiencing (possibly due to my relative unfamiliarity with the normal normalization of the database) is that different user groups have different profiles. Ergo, the seller’s profile will include his commission percentage, while the administrator or customer service does not need this value.

So, will the right method create a unique profile table for each group? (e.g. admin_profiles or salesperson_profiles). or is there a better way that brings together certain details in a common profile, while some users have advanced information. And if so, what is a good example of how to do this with the given commission example?

Answer 1

In this case, when these profiles are really determined by the criteria of the real world, I would suggest using tables dedicated to each type. However, in the interest of full disclosure, you can use the EAV system to store them. Something similar to:

User: UserID ... ProfileID Profile: ProfileID Name Criteria: CriteriaID Name ProfileCriteria: ProfileID CriteriaID UserCriteria: UserID CriteriaID 

The Profile table defines the parent table for profiles. You will have one line for each of your profile types.

Criteria defines the main criteria that can exist in a profile, no matter which profile (for example, you can have the same criteria for multiple profiles).

CriteriaProfile is used to create the m: m relationship between Profile and Criteria . It also means that you add things like sorting for criteria in a specific profile.

UserCriteria points to user values ​​for the specified criteria. It will also allow you to switch profiles for the user and maintain any criteria that are common, simply by deleting those that were not part of the new profile.

HOWEVER

EAV structures have a lot of overhead to maintain. Instead of relying on RDBMS tools to manage structured data (for which people are paid a lot of money to come up with), now you need to manage it yourself. Tables tend to grow significantly faster than systems without EAV, since you have many times more rows (since now you have a row for each column in the normal structure).

EAVs are powerful and flexible, but not always the solution. If your profiles need to be dynamic, then they may be the right tool.

Answer 2

Well, it depends on how different the profile information is. In our case, sellers have much more information that we store compared to other users. We need to know what territory they are located on, the seller or the forces that they are part of, the brands they represent, etc.

If you only have one ro of two pieces of different data, just add the columns and make them null. If you have a lot of additional data, you will need tables for the sales data of other groups. These are many tables with a one-to-one relationship, or from one to many, depending on the nature of the data.

But keep the general profile information that applies to everyone in one place. In the end, you can have users who have several roles (seller and administrator), and therefore you want the basic user information to be stored only once.

Answer 3

If your groups will not change in the long run, I would advise storing a separate table for each profile for each group - admin_profile, salesperson_profile. Create your object model so that all these types of profiles are accessible inside the User object itself.

EDIT

If your object model sounds, you can do the following.

Create the USerProfiles table with foreign links from the USER and Groups table. Add two coloumns, ProfileFieldName and ProfileFieldValue. Also add a primary key field. At run time, continue to add ProfileFieldName and values ​​for each Group and User pair, where applicable. Most importantly, in the User object of your object model, specify the typed properties that address the ProfileFieldName.

I would recommend doing this as soon as possible, as this will cost you more requests and more processing cycles. Building a User object will take comparatively more time and processing in this approach.

Answer 4

The best way to do this is probably the admin_profiles table, the salesperson_profiles table, etc. Each table must have a foreign key that references the main table "Users", and any properties that apply to all (or most) types of users must be columns in the table "Users".

I assume that the list of different roles will be fixed, i.e. that new, arbitrary role types will not be added to system users. If that were the case, you would probably be in EAV, which, I understand, is not very fun.

Answer 5

Another option that was used by other suppliers is the presence of only 2 tables. The list lists the attributes that define the profile, such as disk space, number of rows returned, etc. Other lists have groups that have this option. Oracle does something very similar.

This makes it easier to expand than other methods, but limits you to how you add new pairs of names and values ​​to the structure. For example, if there are 3 elements in a name-value pair, you may have problems. But I think this is a simpler, cleaner, more scalable and simpler approach.

Answer 6

It’s best to use the user_meta table, which stores the user ID, meta name (commission or some other value that you want to save) and the meta value.

Thus, you can add and remove fields without changing the database, and you also do not have to look for different tables.

So you may have

  user_id | meta_name | meta_value 10 | commission | 1.5% 50 | CS rating | 85% 

If user number 10 is a seller and user number 50 is a customer service representative.