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.
To pull all users under any level of office
-- set up working table to hold offices
where officeId = @office
-- loop adding child offices
while( @@rowcount > 0 )
from Offices o,
where o.parentOfficeId = t.officeId
and not exists(
from #TempOffices t2
where t2.officeId = o.officeId )
-- extract employees under these offices
from #TempOffices t,
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
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.