Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am trying to design a database schema for a web app which has 3 subdomains: a) internal employees b) clients c) contractors. The users will be able to communicate with each other to some degree, and there may be some resources that overlap between them. My primary question is if I should split up the roles into separate tables for each domain, as in internal_roles, client_roles, and expert_roles, or if I should just keep them in the one roles table, with a user_type field to differentiate the roles. Also, I'm wondering if having this type field will cause it to violate the 3rd normal form? Really appreciate your time and thoughts on this. Cheers!


--

-- Table structure for table locations

CREATE TABLE IF NOT EXISTS locations ( id bigint(20) NOT NULL, name varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--

-- Table structure for table privileges

CREATE TABLE IF NOT EXISTS privileges ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, resource_id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;


--

-- Table structure for table resources

CREATE TABLE IF NOT EXISTS resources ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, user_type enum('internal','client','expert') NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


--

-- Table structure for table roles

CREATE TABLE IF NOT EXISTS roles ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, type enum('position','department') NOT NULL, parent_id int(11) DEFAULT NULL, user_type enum('internal','client','expert') NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


--

-- Table structure for table role_perms

CREATE TABLE IF NOT EXISTS role_perms ( id int(11) NOT NULL AUTO_INCREMENT, role_id int(11) NOT NULL, privilege_id int(11) NOT NULL, mode varchar(250) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


--

-- Table structure for table users

CREATE TABLE IF NOT EXISTS users ( id int(10) unsigned NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, password varchar(255) NOT NULL, salt varchar(255) NOT NULL, type enum('internal','client','expert') NOT NULL, first_name varchar(255) NOT NULL, last_name varchar(255) NOT NULL, location_id int(11) NOT NULL, phone varchar(255) NOT NULL, status enum('active','inactive') NOT NULL DEFAULT 'active', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


--

-- Table structure for table user_perms

CREATE TABLE IF NOT EXISTS user_perms ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, privilege_id int(11) NOT NULL, mode varchar(250) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


--

-- Table structure for table user_roles

CREATE TABLE IF NOT EXISTS user_roles ( id int(11) NOT NULL, user_id int(11) NOT NULL, role_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

share|improve this question
    
It would help if you were more specific about the kind of feedback you are looking for. The more specific the question, the better chance of a potential answer focusing on your concern. –  JustinC Mar 11 '11 at 1:51
    
Hey JustinC, thx for the advice and taking the time out to read my question. My primary question is if I should split up the roles into separate tables for each domain, as in internal_roles, client_roles, and expert_roles, or if I should just keep them in the one roles table, with a user_type field to differentiate the roles. Also, I'm wondering if having this type field will cause it to violate the 3rd normal form? –  blacktie24 Mar 11 '11 at 2:20
    
Thank you for clarifying that. It is recommended that you edit and update your original question to reflect what you have noted in comments. –  JustinC Mar 11 '11 at 2:52
add comment

1 Answer 1

On quick scan of a few immediate potential concerns..

Field type/dimension mismatches between primary key and presumably a foreign key. Consider aligning the field type to one size or the other:

  • location.id and users.location_id (bigint(20) vs int(11));
  • users.id and user_perms.user_id and other presumably foreign keys (int(10) vs int(11))

Duplicate enum definition across several fields in different tables. Consider making this a seperate lookup table:

  • users.type, roles.user_type, resources.user_type (internal, client, expert)

As a secondary note about naming, I would strongly consider naming these resources to closely match the domain. If the resource is a contractor, name it a contractor. Expert implies a high skill level, and when interpruted by a reasonable person who doesn't know the underlying meaning in the system, could take that to mean something much different, as you would certainly have internal experts, and client experts.)

Initial Auto_Increment values seem strange. Usually if I choose to not start incrementing at one (1), I would start at a much higher number ~ 100, 1000. There are reasons to assign random keys to prevent 'mining', but this isn't the mechanism. Is there are a particular reason for starting at 2, 3, or 4?

Similar issues for other fields migh exist. Those I listed caught my eye first.

EDIT:

Upon clarification of you question, I might suggest that 'it depends' as to whether to split the roles to seperate tables depending on site, and if so, how. Because they are all roles, and because you intend to provide cross subdomain site interaction/communication, they won't be completely distinct roles, and you probably would not necessarily need a distinct method of using or managing them. The term multi-tenancy applies here.

What you have for roles now is essentially a 'table per heirarchy' to represent different kinds of roles. Unless a certain site's concept of a role grows in complexity or diverges from the common role structure of the other sites to the point that the other sites that share the same physical table leave a lot of sparse records, you probably wouldn't want to change that fundamental structure of your table to represent essentially three site role classes (via 'user_type' discriminators of internal, client, and expert). If a role is a role is a role, regardless of the site, what you have is fine; otherwise, a 'table per type' is something you may want to look at.

Discussing the domain a little more, including reiterating what was said originally and in comments..

The user_type, might be described as 'site' 'membership', and each 'site' has their own set of rules about role assignment. There is no overwhelming reason to change your terminology, it's just clearer for me as I internalize it.

An 'internal' user is a member and has access/interaction within the 'internal' site. In fact all users have a 'home' or primary site, and one could refer to that relationship as a site membership. A user with primarily 'internal' membership might have additional limited membership to one or both of the other two sites. A user who is primarily an 'expert' or a contractor would have membership, to the 'expert' or contractor site, and possibly a limited membership to 'internal' site areas (and similarly, but conversely for the 'client' members having limited membership to a different set of internal functions). The limits to membership are based on some policy...

Some roles will probably have common functionality across 'subdomain' sites (like those users who have responsibility for personnel/HR, admin, payroll, managers, common subsystems such as authenticator or notifier or reminder, etc). It is likely that some users may have more than one role, and the extent of a role varies from site to site. As an example, a certain contractor may be responsible for certain aspects of payroll on the 'expert' site (like reporting their own hours, and signing off as verification for the hours of other contractors), and wouldn't have the same set or responsibilities on the 'internal' site.

Knowing which site a user or member primarily belonged to (or perhaps more specifically called 'home'), would determine certain eligibility to global/common roles, primary/home site roles, and to roles with neighboring sites where they have an additional, established, limited membership. The 'internal' site is a neighbor to both the 'client' and 'expert' sites, but the 'expert' and 'client' sites are not neighbors to each other. In other words, there is no direct link between the two, and all interaction between an 'expert' role and a 'client' role happens via some intermediary role within the 'internal' site by an internal role granted to a user with primarily internal membership. There is a certain, specific relationship between two sites, and a relationship of site and a role. Some roles would simply be invalid for certain users, even if they were some how assigned a role because their membership, or lack of membership, precludes the role from being applicible. Some roles would be invalid for certain sites, but other roles, perhaps like 'commentor,' or 'reviewer' might be valid for all sites.

It might make sense to model site to site membership relationships, and then define your roles based on a particular relationship, instead of simply which site they call 'home.' I believe the concept is somewhat of a rudimentary context aware/sensative role based access control (RBAC). I found another post from stackexchangethat asks about a situation similar in some respects to yours, and you may want to consider this drawing for further implementation ideas (internally linked with within that post).

In any case, I hope this was helpful. Implementing effective, managable RBAC can be messy.

share|improve this answer
    
Hey just responded to your other comment about focusing my question. –  blacktie24 Mar 11 '11 at 2:24
    
1) Good point on the field type/dimension mismatches, have been moving around a lot of pieces as i'm in the rough draft phase so I haven't been paying attention to aligning them, should have done that before I posted, another newb mistake. –  blacktie24 Mar 11 '11 at 2:27
    
2) I considered making a lookup table, but I wanted to be able to just write a select statement and use the user_type field as a discriminator without having to consult the lookup table first. So if I used a lookup table I would need to use a JOIN? –  blacktie24 Mar 11 '11 at 2:38
    
3) About the naming, you're right, and thanks for pointing that out and explaining it in detail. 4) The initial auto_increment values are likely the result of me putting in dummy values into the tables and then using the export as SQL option, so it just picked up the auto increment where I left off. But that's interesting about the mining issue, didn't even think about that. –  blacktie24 Mar 11 '11 at 2:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.