Database Schema for Hierarchy

Question

I am looking for a generic open source database schema for a basic login, and user management with roles.

Users: belong to Business Units which belong to Clients.

Users have Roles - Manager, Regional Manager, site manager, worker etc. Users can have multiple roles dependant on the business unit and multiple business units..

Business units belong to Clients. Business units can contain other business units (Region contains other regions which contain business units).

Features: describe behavioral entities in the system. Features are assigned to Users, or to Roles, or both, and also Business Units and/or Clients have features active within the system.

Say for instance it is an accounting system, and has Features such as Payables, Receivables, Reports, General Ledger etc. based on what is active for the Client/Business Unit/Role/User in play, what the user sees is that active matrix hierarchy.

Does anyone have a reference to a schema similar to this that meets those basic requirements? Bonus if it has code for a web based interface (user login, management for the Users, Regions, Roles etc) .Net C# preferred, but I can translate.

Answer 1

Not exactly what you're looking for, but a start:

http://www.databaseanswers.org/data_models/user_identity_management/index.htm

Did you take a look at ASP.NET membership? There's a script that creates all the objects in your .net directory. If you create a web site with the defaults in Visual Studio, there's a web site admin tool to manage users.

Answer 2

How hard is the requirement that this be implemented in a SQL database? There are many designs that will work, but if you're looking for something off-the-shelf, then what you're describing is LDAP, to a tee. That just describes the protocol, but there are many different open-source implementations including Apache Directory Server and OpenLDAP. Wikipedia has an extensive list of LDAP software.

It's something to consider. If you need to tightly integrate this with another SQL database, enforce referential integrity for users and so on, then this might not work for you. But if you're looking for something generic and extensible, this might be the way to go. You'll find that most membership/role databases such as those created by the ASP.NET SQL Server Registration Tool are not hierarchical because, well, loose hierarchies are kind of complicated.

If you need to nest arbitrary numbers of Business Units then managing all the rules and overrides is not a trivial task. That's exactly why we have hierarchical directory protocols.

(Note - If you're already in a Microsoft environment then Active Directory would be the obvious choice; it's not Open Source, but it's probably the most robust and well-supported of any directory server.)