In our new system we have three type of people

  1. Business owner
  2. Assistant of business owner
  3. Staff (a skilled employee)

I am having following entity

  1. [Business] - include detail about business owner
  2. [Assistant] - detail about the assistant
  3. [Staff] - detail about the staff

Business rules

  1. There will be only one business owner for a given business, means there can be only one login for business owner. He is like a super user for his business.
  2. Business owner can be staff as well.
  3. A single business can contains more then one staff and each staff have different kind of rights/permissions can be configured by business owner.
  4. Only one assistant allowed per business and rights/permissions can be configured by business owner.

What would be right way to go?

Any help/suggestion appreciated!!!

  • Is this question about design (model, tables etc) or security in the RDBMS? – gbn Jan 4 '12 at 8:41
  • This is about data model design for application. – Mahesh Jan 4 '12 at 9:33

The standard way to model permissions for a system is through Role-Based Security. The typical model looks something like this:

Role Based Security ERD

The way it works is you have three tangible things: Users, Allowable Actions (the access that is being controlled) and Roles. Roles are groups of both users and allowable actions, therefore there are two intersection tables which record the people in each role and the actions that role permits. Some people include additional information in the ROLE MEMBERSHIP and PERMISSION intersection tables, like when the record was created and by whom. Some people keep that kind of audit log information in a separate table.

In this model every type of user is stored in your USER table. The rules that you have stated for cardinality of users (one owner, one assistant) are frankly very brittle if you ask me. I don't like to cast rules into my data model which are almost certainly going to change. It seems unreasonable to me that a business will use your system if it doesn't allow a manager to delegate their responsibilities for when they are off duty, on vacation or away from the business. Your cardinality rules should be enforced in application logic or with additional tables related to USER if you really feel they need to be in the data model somehow.

Your Answer

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

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