I'm building the core tables for a small, cloud-based enterprise app.

Is this data model suitable for a role based authentication system (flat hierarchy of permissions) that allows users to work for multiple companies?

data model


As pointed out by Joel Brown, I didn't state my exact requirements. Here there are:

  1. This app is for a startup and specs change quite often, so the model should be flexible and expandable. The underlying idea is to create enterprise software (think ERP).
  2. One User can work for multiple Organizations with multiple Roles & Permissions (imagine a chain of hotels, each hotel is an Organization, and the top management of the chain has access to various Organizations)
  3. Permissions can be flat (ie no hierarchy).
  • You don't state your exact requirements, so it is difficult to critique your model. This model will work fine if all you need is to handle one or more role assignments per user where these roles can be in one or more organizations. It is flexible insofar as it permits different role assignments for each organization. Some people would question the need for employments.id since this table is a pure intersection and you could just as easily use a compound primary key for employments. Some might point out that this could have performance benefits when checking user roles. – Joel Brown Jul 1 '13 at 11:35
  • Thanks Joel, I updated the post. Having a 1:n relationship between User and Organization would not allow the User to work for multiple Organizations, correct? – Mike81 Jul 1 '13 at 11:57
  • Mike81 - Changing the primary key of employments to the combination of users_id and organizations_id doesn't change the relationships in any way. The employments table is still a many-to-many intersection table, so each user can belong to many organizations and the role assignments for employees are still organization-specific. It would mean propagating user and organization columns to your role_assignments table, which would allow direct joins for convenience. Your FK constraint would from role_assignments would still be to employments though. – Joel Brown Jul 1 '13 at 13:27
  • Thanks Joel. I read up on compound primary keys. Very interesting, I never heard about it before. Unfortunately, my framework (Rails) does not seem to support compound primary keys. – Mike81 Jul 1 '13 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.