Sql-server – Storing overridable hierarchical settings

hierarchysql server

I've searched the Internet and found the question Pattern(s) about hierarchical settings overwriting on Software Engineering SE. It exactly describes my need, the only difference is that I need to store the settings in a SQL Server database.

How can I implement it in SQL Server?

Whenever I select the setting for level1-child1, I want all overridden settings from child1, plus inherited ones from level1. For example:

level1: max-value:100;  min-value:10;  enable-alarm:true;
level-child1: min-value:30;

after select:

level1-child1: max-value:100; min-value:30; enable-alarm:true;

Currently I use hierarchy path to implement hierarchies.

Best Answer

You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like /<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than needing to update the DB structure when a new setting is added to the applcation logic, resulting insomething like this:

Nodes                 NodeSettings
=============         ============      Settings
NodeID   (PK) <--.--  NodeID  (FK)      =========
ParentID (FK) ---'    Name    (FK) ---> Name (PK)
FullPath              Value             
OtherProp1
OtherProp2

In this arrangement you can pull all settings that have a value anywhere up the tree from a given location (with values closer like so:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 ns.value
                FROM   NodeSetting ns
                JOIN   Nodes n ON n.NodeID = ns.NodeID AND '<PathToTargetNode>' LIKE n.FullPath+'%'
                WHERE  ns.Name = s.Name
                ORDER BY LEN(n.FullPath) DESC
               )
FROM   Settings s

The use of a sub-query like this my not be efficient enough if you have many settings but whould be fine otherwise. Of more concern is using LIKE that way: it will not be able to properly use any index on Nodes.FullPath so an index scan (or worse, a table scan) could be performed for each call of the sub-query. Off the top of my head I can't think of a way to significantly optimise this as a single query without adding new structure to your storage of the relationships between nodes such as keeping map between ascendants and descendants like:

RelationshipDistance
=======================
Ascendant  (FK->NodeID)
Descendant (FK->NodeID)
Distance

(making sure to store the node's relationship with itself, as AscNode='NodeID', DescNode='NodeID', Distance=0). Then you can do:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 s2.value
                FROM   RelationshipDistance r
                JOIN   Settings s2 ON n.NodeID = s.NodeID AND s2.Name = s.Name
                WHERE  r.Descendant = '<IDOfNodeYouAreLookingFor>'
                ORDER BY r.Distance DESC
               )
FROM   Setting s

The disadvantage here is needing to maintain the extra structure (through triggers or work in another layer of your code).

Of course this is assuming that you want to return the settings from a single statement. You could always define a procedure that walks up the path collecting the settings in a temporary table or table variable, adding new settings found at each step towards the root - this is likely to be more efficient than a single statement version that causes index/table scans.

Related Question