Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!!!

share|improve this question
    
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
add comment

1 Answer

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.

share|improve this answer
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.