Databases
StackOverflow2013
Postsdbo
POHierarchical SQL data (Recursive CTE vs HierarchyID vs closure table)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHierarchical SQL data (Recursive CTE vs HierarchyID vs closure table)
    text
    copied!<p>I have a set of hierarchical data being used in a SQL Server database. The data is stored with a guid as the primary key, and a parentGuid as a foreign key pointing to the objects immediate parent. I access the data most often through Entity Framework in a WebApi project. To make the situation a little more complex I also need to manage permission based on this hierarchy such that a permission applied to a parent applies to all of its descendants. My question is this:</p> <p>I have searched all over and cannot decide which would be best to handle this situation. I know I have the following options. </p> <ol> <li>I can create <code>Recursive CTEs</code>, Common Table Expression, (aka RCTE) to handle the hierarchical data. This seems to be the most simple approach for normal access, but I'm worried it may be slow when used to determine permission levels for child objects.</li> <li>I can create a <code>hierarchyId</code> data type field in the table and use SQL Server provided functions such as <code>GetAncestor()</code>, <code>IsDescendantOf()</code>, and etc. This seems like it would make querying fairly easy, but seems to require a fairly complex insert/update trigger to keep the hierarchyId field correct through inserts and moves</li> <li>I can create a <code>closure table</code>, which would store all of the relationships in the table. I imagine it as such: parent column and child column, each parent -> child relationship would be represented. (ie 1->2 2->3 would be represented in the database as 1-2, 1-3, 2-3). The downside is that this requires insert, update, and delete triggers even though they are fairly simple, and this method generates a lot of records.</li> </ol> <p>I have tried searching all over and can't find anything giving any advice between these three methods.</p> <p>PS I am also open to any alternative solutions to this problem</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload