Structuring group membership

Question

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?

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.