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.

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:

  1. Account Level
  2. Department Level
  3. 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?

share|improve this question
    
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. –  JHFB Jan 8 '13 at 12:52
add comment

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.

share|improve this answer
    
LedgerSMB is PostgreSQL only but WITH RECURSIVE is supported on SQL Server so it should be the same approach. –  Chris Travers Oct 10 '12 at 11:39
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.