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.

Well i am reconstructing an already created application and was looking to improve this, especially with in the database structure.

I want to start off with probably the biggest section and that is the users/clients section.

A bit of History Here is a descriptive explanation of the structure i want to achieve or how it needs to work.

Users: the users can fit into say 4 categories: Admin, Sub-Admin, Client(Single, Multi)

Admin An admin can have access to All clients and their details

Sub-Admin A sub-admin can only have access to clients that have been assigned to them from 1 to many

Clients A client can be set up as a single client or A client can be set up as a multiple client

A single client can only look after their own items etc A multiple client can be assigned many single clients and can be look after all their clients items

The current application uses the above system except for the multi client section. So they have ADmin, Sub-Admin , Clients(single)

The Admin section is not a problem as i can have them list every client and their items. Its when i get to the Sub-Admin and clients i am looking to refine etc.

The structure i have for the current operation is

      (A)                (B)                   (C)                   (D)
+--------------+   +----------------+    +----------------+    +----------------+
| User table   |   | Client table   |    | User /Client   |    | client items   |
+--------------+   +----------------+    +----------------+    +----------------+
| id           |   | id             |    | user_id        |    | id             | 
|              |   |                |    | client_id      |    | client_id      | 
| more...      |   | more....       |    +----------------+    +----------------+
+--------------+   +----------------+                          

At the moment, when a user logs in, i would get their id form the table(A), then get all the clients associated to this user from table(C), then using the client(s) id list all the items table(D) associated with those client(s)

My problem is with adding the next client level (multi).

Would it be best to have these within a separate table or could i add these to the same client table.

'Clients (B)' have many items(D) 'User (A)' has many clients (sub-admin/multi Client) 'User (A)' has a single client (Single CLient) The admin user has access to all clients and items

I was thinking of having them as a separate table 'sub-clients' but then as i rely on the 'client_id' to get my items list fo the selected client, i thought best to add them to th esame table (B) so that ll clients are in the same table.

So then i got to then thinking how can i manage this.I would introduced another table to link a client to its clients, to try and give you an idea.

       (E)
 +----------------+
 | client/client  |
 +----------------+
 | client_id (B)  |
 | client_id      |
 +----------------+

So how this new table would work (E) would be:

When the user logs in i then get the client_id(s) for that user form the(C) table, then using this/these client_id(s) i would then look into the (E) table and get each client associated with the users client_id(s).

Within the (E) table the first client_id would always ref. itself then if there are any clients associate to that client then they can be set here. This way then i can using a loop get all the items form the (D) table relating to ALL the users associated client(s).

I hope this makes sense as it does in my head as such, but if there is a better way you could think of that this could be achieved, as with table (E) i am not sure if it can ref the same table as as keys?

Much appreciated.

EDIT/UPDATE I have this in the comments but incase its vital i have also added it here:

The admin can do everything all other users can do plus add/edit delete users and a few more things. A sub admin can have many clients and if those clients are multiple clients the sub admins have control over those as well. A single client only has control over the items it creates, a multiple client can have control of all their clients and their items. A multiple client is similar to a sub-admin but a sub-admin will also have addition items to do thats why i did not just do it as admin, sub-admin, clients (where multiple clients are sub-admins) hope this helps

I could use the multi client as a sub admin and just within the users table have a auth_id value that stipulates what they can and cannot do so this could difference the two?

share|improve this question
    
Can multiple admins manage the same sub-admin? Can multiple sub-admins manage the same client? Or is this strictly a single manager per client scenario? –  Max Vernon Mar 28 '13 at 15:08
    
The admin can do everything all other users can do plus add/edit delete users and a few more things. A sub admin can have many clients and if those clients are multiple clients the sub admins have control over those as well. A single client only has control over the items it creates, a multiple client can have control of all their clients and their items. A multiple client is similar to a sub-admin but a sub-admin will also have addition items to do thats why i did not just do it as admin, sub-admin, clients (where multiple clients are sub-admins) hope this helps –  Simon Davies Mar 28 '13 at 15:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.