Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008

    User Management Design Question

    Hi Guys,

    I have a db design question which has me going round in circles a bit.

    We have a 3 tier user management system in our application.
    Up to now its been fine but now our clients want to expand the user types.

    At the moment we would have the following

    Account Admin A
    ->Company A
    ->->Employees 1..N
    ->Company B
    ->->Employee 1..N
    Account Admin B
    ->Company C .....

    Now as we are moving to a more comprehensive solution we have been asked to provide a more expansive user management model for example

    Account Admin
    ->Company A
    ->->Office Admin
    ->->Regional Admin
    ->->->Regional Office 1
    ->->->->Employee 1..N
    ->->->Regional Office 2
    ->->->->Employee 1..N
    ... etc etc ...

    Currently all users are in the same user table with an admin key which links to the admin directly above in the chain for example Company B will have an adminid of the PK for Account Admin A etc ...

    Now as this is getting more complicated I want to step back and get advice on the best way to design the user management database.

    The problem is, I dont know whether to break them all up into separate tables or to leave them in the one big table with links to the user next up in the chain? breaking up into separate tables means nice logical model but tons of joins... leaving everything in the same table gets very complicated ... hmmm ...

    Can anyone point me to useful design patterns for multi tier user management db design ?

    Any advice from anyone who has faced a similar decision would be very much appreciated.

    Thanks in advance guys.

    Kind Regards,

  2. #2
    Join Date
    Jun 2007

    Assuming your tables are similar to
    To pull all users under any level of office

    -- set up working table to hold offices
    select  officeId
    into     #TempOffices
    from    Offices
    where  officeId = @office
    -- loop adding child offices
    while( @@rowcount > 0 )
        insert  #TempOffices
        select  o.officeId
        from    Offices o,
                  #TempOffices t
        where  o.parentOfficeId = t.officeId
                  and not exists(
                         select  1
                         from    #TempOffices t2
                         where  t2.officeId = o.officeId )
    -- extract employees under these offices
    from      #TempOffices t,
                Employees e
    where    e.officeId = t.officeId
    -- clean up
    drop table #TempOffices
    It's not difficult to use a hierarchy like this - the above will work for any level of office but it does assume an office has just one parent (you'd need to add a separate relationship table if this isn't the case). The code is sybase but I don't have a database at hand to try it out on. The loop adds new offices under the offices already gathered so it may loop 1 - 3 times depending on what your starting office is.

    If you had separate tables holding the employees in each office then the difficulty is then deciding which tables to include. This put's the complexity into the code (deciding which tables to use) rather than once in the database.

    Account Admin A
    ->Company A
    ->->Employees 1..N
    ->Company B
    ->->Employee 1..N
    Small point but I assume the above example doesn't mean two employees could have empId 1? - if it does then you want to get rid of that straight away.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts