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.

I'm trying to figure out how to set up the structure for users.

Users can have multiple Managers and Managers can manage multiple Business Units. Administrators have full control of the system.

I'm trying to figure out if I should separate the Users and Managers from the User table and have a table for Administrators and Managers or separate all three.

The Managers have multiple Business Units which a User can be managed by.

Total amount of users could be estimated about 1,000. What do you think the best approach to this?

share|improve this question
    
Are you asking how to do authorization between these roles, or an organization map? –  Vidar Nordnes Apr 13 '12 at 17:40
    
I'm trying to set up a login and organizational levels. I have had them separated, but I feel like I'm creating duplicated data and extensive work for the logic part of this. –  Allen Tong Apr 13 '12 at 17:55
    
thanks for the help! –  Allen Tong Apr 13 '12 at 20:18
add comment

migrated from stackoverflow.com Apr 14 '12 at 19:50

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

I think the best thing to do is this: 1. Have a User table. 2. Have a User_Role table. 3. Have a User_Assigned_Role table.

Since every user of the system is a user. There is no need separating users table-wise, that means if there is a new requirement tomorrow another table will emerge.

You can create roles like Student, Manager, Administrator and assign resources to these roles and afterwards assign the roles to users. So if you want Mr. A to be an Administrator, assign an administrator role to him.

Table Design

User Table
--------------
id
user_name
password
firs_name ...

Role Table
---------------
id
role_name
role_description
role_status (To deactivate or activate a role)

User Assigned Role Table
----------------------------
id
user_id (References User Table)
role_id (Reference Role Table)

Hope this solves your problem. Contact me if you need more clarification.

share|improve this answer
    
This is a great example, but everyone is missing the fact there is a Business Unit Table. I will edit for a better understanding. –  Allen Tong Apr 13 '12 at 17:51
1  
If there is a business unit table, then every user needs to have a specific role to play in a specific business unit. Then all you need to do is to add a business unit id that references your business unit table to User_Assigned_role table. Then when you are assigning a role to a user, you select the business unit he will need to play that role. That way since every role knows what to do or the activities assigned to it, when a user logs in, present him with a table of roles (two columns: rolename n business unit). once he selects one, go ahead and load what he can do with the role. –  Uchenna Apr 13 '12 at 18:12
    
You need a business Unit table. i forgot to add that. –  Uchenna Apr 13 '12 at 18:18
    
I went with your solution :) –  Allen Tong Apr 13 '12 at 20:17
add comment

Is an administrator a special kind of user, or does it have a relationship to a user? If there are many to many relationships, there should be a table to keep track of the relationship, if not, you can just have an "administrator" flag.

And it sounds like you should definitely have an additional table for the relationships for the manager.

share|improve this answer
    
An Administrator is just the admin that controls the overall system, which is unlikely to be a user. A user is just a person who works under the managers. –  Allen Tong Apr 13 '12 at 17:41
    
Well then you definitely want a separate table for them. Is a manager a kind of user? You could have a separate table for them, or just a flag. Presumably then for users you'll want a "works under" fk to a manager/user, and a table for business units with an fk to a manager/user. –  Colleen Apr 13 '12 at 17:45
    
A manager will never be a user of this, so I assume special. A user can have also have different managers with a different business unit. So I was trying to put Users and Managers/Admin in different tables. Then, a business unit can related to a manager [Business Unit/Manager], which a user can be related to [User/Manager/Business Unit]. –  Allen Tong Apr 13 '12 at 18:00
    
Ah, so Users can also be related to business units? This is sounding like you're going to want a user table, an administrator table, a manager table, a business unit table, and a table to represent each many to many relationship between those 4. –  Colleen Apr 13 '12 at 18:03
    
btw, when you say "a manager will never be a user" does that mean that they actually won't be in the system at all (e.g. won't have a username/login) or do you mean the other direction, that a user will never have the same permissions as a manager? Because if it's just a matter of permissions, I'm inclined to agree with the other answer that you could lump them together and just give them a type fk to another table with role name and permissions. –  Colleen Apr 13 '12 at 18:05
show 4 more comments

Users

  • UserID (PK)
  • isAdmin (boolean)
  • FirstName
  • LastName

UserManager --> uses Hierarchical queries to get beyond 1 level

  • UserID (FK to users)
  • ManagerUserID (FK to Users)
  • BusinessUnitID (FK to BusinessUnit)

BusinessUnit

  • BusinessUnitID
  • Name

Reasons:

  • Users, admins, and managers all share properties such as first name, last name, etc. Normal form says not to repeat data you would be if you had multiple tables.
  • If a user gets promoted to a manager, you have to move a bunch of data around. This way you don't.
share|improve this answer
    
After reading Uchenna's response if you do in fact have more levels of security then just admin, I would add a Role table and have a userrole table as well like suggested. –  xQbert Apr 13 '12 at 17:51
    
How would I find out if the User works under that manager? –  Allen Tong Apr 13 '12 at 18:06
    
join user to usermanager twice; once to get the employee's name, second to get a manager's name. So SELECT FirstName, LastName from Users U INNER JOIN userManager UM on U.UserID=UM.UserID WHERE ManagerUserID='123' would give you a list of all users under a manager. or you could write a function that returns true/false if conditions are met. –  xQbert Apr 13 '12 at 18:26
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.