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.

I have more of a conceptual question if anyone has examples or some ideas of how others have accomplished this.

On a sales reporting site, a user should be able to pull information based on the following hierarchical structure:

Account Level

Department Level

Division Level

The categories are encompassing as you move up the chain (ie. Dept includes all accounts within that group). A user could have access to several groups within each tier. But a user would not have cross-category filters (ie. both a Department & Division level filter).

Would all this be accomplished in a single table with a column indicating the filter level and several rows per user with each criteria? What other suggestions are there?

It is worth a mention that if you are serving these reports up through a BI tool like BusinessObjects or MicroStrategy, you can build some of these limits into the tool's security set up. Depending on your environment and skill sets, it may be a better option.
–
JHFBJan 8 '13 at 12:52

1 Answer
1

What we do in LedgerSMB is to have a system of business reporting dimensions (two tables, one of which establishes the dimensions and the second establishes the hierarchies of each dimension). In your case you could probably just have one table of departments, with a self join to generate the hierarchy (WITH RECURSIVE CTE in a select statement would generate it).

Then you can have a view which generates your hierarchy for it, and you can link your accounts to groups.

This way they can be arbitrarily nested. You aren't limited to three levels. You could have 5 or 10 levels with some performance cost and you don't have to change your data.