My application database includes a Groups table that separates customers into logical roles and defines access levels (admin, owner, sales rep, customer support, etc.)
Groups has numerous Customers. The Customers table consists of login particulars for example account information.
Now If only to include user profiles to my database. The problem I am getting (most likely because of my relative unfamiliarity with proper database normalization) is the fact that different user groups have different types of profiles. Ergo, a salesperson's profile includes his commission percentage, whereas an admin or customer support wouldn't need this value.
So, would the correct method be to produce a unique profile table for every group? (e.g. admin_profiles, or sales rep_profiles). or it is possible to better method in which combines certain particulars inside a generic profile, although some customers have extended info. And when so, what is among how to get this done using the commission example given?
Well that is dependent how differnt the profile details are. Within our situation, salesmen have numerous more bits of information that people store in comparison with other customers. We have to be aware of territory tory they're in, the sales pressure or forces thay have been in, the brands they represent, etc.
Should you have only one ro two bits of differnt data, just add the posts making them nullable. Should you havea procuring data, you'll need tables for that sales rep data vice other groups. These many be tables having a one-to 1 relationship a treadmill to a lot of depending onteh character from the data.
But keep your general profile information that is applicable to on this page. Eventually you might have customers who've multiple roles (sales rep and admin) which means you want the fundamental user information saved only one time.
In case your groups will not alternation in long term, I'd advise to help keep seperate table for every profile for every group - admin_profile, sales rep_profile. Design your object model in ways that these profile-types can be found within User object itself.
In case your object model is seem, you'll be able to do following.
Produce a table USerProfiles with Foreign references from USer and Groups table. Add two coloumns, ProfileFieldName and ProfileFieldValue. Include a principal key area too. At runtime, continue adding ProfileFieldName and Values for every set of Group and User, wherever relevant. Most significantly, in User object of the object model, provide typed-qualities that address ProfileFieldName.
I'd still recommend to get it done the prior way since doing the work by doing this would set you back more queries and much more processing cycles. Building User object would take comparatively additional time and processing within this approach.
The easiest way to do it most likely is to possess a table
admin_profiles, a table
salesperson_profiles, and so forth. Each table must have an overseas key referencing the fundamental "Customers" table, and then any qualities that affect all (or most) kinds of customers ought to be posts within the "Customers" table.
I suppose the listing of different roles will probably be fixed, i.e. that new, arbitrary kinds of roles will not need to be added by system customers. In the event that were the situation you'd most likely maintain EAV territory, that we understand is not very fun.
An alternative choice, that has been utilized by other suppliers, would be to only have 2 tables. One lists the characteristics that comprise the profile, for example disk space, quantity of rows came back, etc. Another lists which groups obtain that option. Oracle does something much the same.
This enables for expansion simpler compared to other techniques, but limits you in the way you add new title-value pairs towards the structure. For instance, if you will find 3 elements within the title-value pair, you may be in danger. However I think this is actually the simpler, cleaner, more scalable, and much easier approach.