In almost every enterprise scale system there are some tree structures to be held in the database. It could be a system menu structure, an enterprise organisation structure or a user hierarchy. Lets assume that we need to model insurance agents hierarchy for insurance policies processing system.

Thinking about agents as children and parents and creating such structures seems to be natural:

Agents table

Structure Visualization

If we use this model we need to use recurrency to get some part of such tree i.e. to get agent with ID “2” and all its descendants. Problem is – recurrency in DBs is generally a bad thing because of its performance.

But it is not said that the database structure must be 100% normalized – sometimes redundancy is our only chance to gain some performance. Lets add two additional, redundant columns and keep them up to date by recalculating their values whenever our three changes (new objects are added, existing ones are removed or replaced):

Improved Agents table

Structure Vizualisation

The advantage of this structure is that we are able to select a part of the tree structure in single (and simple) T-SQL query.

To select agent with ID “2” we can execute the following query:

select * from Agents

where Path like '%/2/%'

or ID = 2

To select all agent’s with ID “1” children (not descendants) we can execute the following:

select * from Agents

where Path like '%/1/%'

and Depth = 1

Simple and efficient. In order not to be groundless I will mention that Umbraco CMS successfully uses this approach.

And it works great with Linq2SQL:

context.GetTable<Agent>()

.Where(x => x.Depth = 1 && x.Path.Contains("/2/"))

.ToList();