Netsted Set Model - Permissions?

[elhaix]

Has anyone implemented a nested set model for permissions?
ie. Human Resources app.

For example, managers can view their own employee's data, but not other managers' or employees' data on a tree.

Any links/examples of implementing this?


Thanks.

 

[jmeckley]

in more general terms you are looking for a tree of data where access flows down, not up. not across.

there are a couple options. the most common is to store the tree in a single table
tree
id (int, pk)
description (text)
parent (nullable int, fk to tree.id)

there are other schemas too, but i can't remember them now.

how to query this depends on 3 things
1. the schema you choose
2. how much total data is there
3. how much data do you need

in smaller scenarios just load the entire table, build the tree, and then query the object tree. if the data tree is very large preformance will suffer.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[elhaix]

In terms of data storage, using a nested set model (

http://www.developersdex.com/gurus/articles/112.asp)

vs. adjacency list model is the way to go.

Then retrieving the hierarchy through CTE (TSQL's Common Table Expression) will retrieve the list -

http://sqltips.wordpress.com/2007/05/17/retrieving-employee-hierarchy-through-cte/

.

The problem comes in when a node is deleted. For that I'm thinking of doing a cascading TSQL sproc for orphaned nodes.

Any thoughts?

 

[jmeckley]

In terms of data storage, using a nested set model (

http://www.developersdex.com/gurus/articles/112.asp)

vs. adjacency list model is the way to go.

that's the other method I saw.

I this method is very database centric. I find that is more difficult to unit test and not as flexible for domain design. I would go the route of DDD and let the object define the database. This could probably be used in conjunction with the adjacency data structure.

Jason Meckley
Programmer
Specialty Bakers, Inc.