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

Question

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

Groups has many Users. The Users table contains login details such as username and password.

Now I wish to add user profiles to my database. The trouble I'm having (probably due to my relative unfamiliarity with proper database normalization) is that different user groups have different kinds of profiles. Ergo, a salesperson's profile will include his commission percentage, whereas an admin or customer service would not need this value.

So, would the proper method be to create a unique profile table for each group? (e.g. admin_profiles, or salesperson_profiles). or is there a better way that combines certain details in a generic profile, while some users have extended info. And if so, whats a good example of how to do this with the commission example given?

Answer 1

If your groups are not going to change in long run, I'd advise to keep seperate table for each profile for each group - admin_profile, salesperson_profile. Design your object model in a way that all these profile-types are available within User object itself.

EDIT

If your object model is sound, then you can do following.

Create a table USerProfiles with Foreign references from USer and Groups table. Add two coloumns, ProfileFieldName and ProfileFieldValue. Also add a primary key field as well. At runtime, keep on adding ProfileFieldName and Values for each pair of Group and User, wherever applicable. Most importantly, in User object of your object model, provide typed-properties that address ProfileFieldName.

I'd still recommend to do it the previous way since doing it this way would cost you more queries and more processing cycles. Building User object would take comparatively more time and processing in this approach.

Answer 2

In a case like this where these profiles are truly defined by real-world criteria, I would probably suggest using tables dedicated to each type. In the interest of full disclosure, though, you could use an EAV system to store these. Something akin 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'd have one row in this for each of your profile types.

Criteria defines the basic criteria that can exist on a profile, regardless of which profile (for example, you may have the same criteria on more than one profile).

CriteriaProfile serves to create a m:m relationship between Profile and Criteria. This is also where you'd add things like sorting for a criteria within a particular profile.

UserCriteria points to a user's specific values for a given criteria. This would also allow you to switch profiles for a user and maintain any criteria that were common by just deleting those that were not part of the new profile.

HOWEVER

EAV structures come with a lot of overhead to maintain. Instead of relying on the RDBMS's facilities for managing structured data (which is what people are paid a lot of money to come up with), you now have to manage it yourself. Tables tend to get very large substantially faster than with non-EAV systems, as you have many times the number of rows (as you now have a row for what would have been every column in a normal structure).

EAV's are powerful and flexible, but not always the solution. If your profiles need to be dynamic, then they can be a suitable tool.

Answer 3

Your best bet is to use a user_meta table that stores the user's id, the meta name (commission or some other value you want to store), and the meta value.

This way, you can add and remove fields without ever having to change the database, and you also won't have to keep looking at different tables.

So you could have

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

Where user #10 is a salesperson and user #50 is a customer service representative.

Answer 4

Another option, which has been used by other vendors, is to have only 2 tables. One lists the attributes that define the profile, such as disk space, number of rows returned, etc. The other lists which groups have that option. Oracle does something very similar.

This allows for expansion easier than the other methods, but limits you in how you add new name-value pairs to the structure. For example, if there are 3 elements in the name-value pair, you might be in trouble. But I think this is the easier, cleaner, more scalable, and simpler approach.

Answer 5

The best way of doing it probably is to have a table admin_profiles, a table salesperson_profiles, and so on. Each table should have a foreign key referencing the basic "Users" table, and any properties that apply to all (or most) types of users should be columns in the "Users" table.

I assume that the list of different roles is going to be fixed, i.e. that new, arbitrary types of roles won't have to be added by system users. If that were the case you'd probably be in EAV territory, which I understand isn't very fun.

Answer 6

Well that depends on how differnt the profile information is. In our case, salespeople have many more pieces of information that we store compared to other users. We need to know the territory tory they are in, the sales force or forces thay are in, the brands they represent, etc.

If you only have one ro two pieces of differnt data, just add the columns and make them nullable. If you havea a lot of extra data, you will need tables for the salesperson data vice other groups. These many be tables with a one-to one relationship or one to many depending onteh nature of the data.

But keep the general profile information that applies to all in one place. Eventually you may have users who have multiple roles (salesperson and admin) and so you want the basic user information stored only once.