Users Hierarchy Logic

I am writi开发者_StackOverflow中文版ng a user security module using SQLServer 2008 so threfore need to design a database accordingly.

Formally I had Userinfo table with UserID, Username and ParentID to build a recursion and populated tree to represent hierarchy but now I have following criteria which I need to develop.

I have now USERS, ADMINISTRATORS and GROUPS.

Each node in the user hierarchy is either a user, administrator or group.

User

Someone who has login access to my application

Administrator

A user who may also manage all their child user accounts (and their children etc) This may include creating new users and assigning permissions to those users. There is no limit to the number of administrators in user structure. The higher up in the hierarchy that I go administrators have more child accounts to manage which include other child administrators.

Group

A user account can be designated as a group. This will be an account which is used to group one or more users together so that they can be manage as a unit. But no one can login to my application using a group account.

This is how I want to create structure

                       Super Administrator
                         administrator
      -------------------------------------------------------------
      |                         |                                  |
   Manager A                 Manager B                        Manager C
(adminstrator)            (administrator)                  (administrator)
                                |
               -----------------------------------------
               |                |                      |
         Employee A         Employee B            Sales Employees
          (User)               (User)               (Group)
                                                       |
                                             ------------------------
                                             |         |            |
                                         Emp C       Emp D        Emp E
                                        (User)       (User)       (User)

Now how to build the table structure to achieve this. Do I need to create Users table alongwith Group table or what?

Please guide I would really appreciate.

---

I may write down a more complete answer later, but I should begin by pointing out that a user is really a special case of an administrator, specifically an administrator who has no child users. I would probably design the users with this in mind.