Modeling Hierarchies In A Relational Database

Modeling Hierarchies In A Relational Database

I’m quite sure that you have encountered hierarchical relationships in some data of your organisation. Popular examples are:

  • Sales-Regions (like cities, countries and continents)
  • Employee-Organigram (who is reporting to who)
  • many more

Basically we can model such hierarchies in the following different ways:

  • Several columns in one table
  • Several tables
  • One self-joining table

Let’s take a closer look onto those approaches: Continue reading “Modeling Hierarchies In A Relational Database”

How to: Install sample database

You want to try out the samples posted on this blog or just play around with different models and data of hierarchies and graphs? Here is the right place to start!

First of all, download the scripts from the accompanying github repository.

Before you start executing all of the scripts, please check, which version of SQL Server you have available. A short


executed in SQL Server Management Studio should tell you, if you have version 2017 (or later) at hand, or a version before.

On my demo machine this statement shows me, that I have SQL Server 2017 installed:

Microsoft SQL Server 2017 (RC1) - 14.0.800.90 (X64) Jul 11 2017 07:03:16 Copyright (C) 2017 Microsoft Corporation. All rights reserved. Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 15063: )

If you have version 2016 or older installed, please execute the following scripts (GraphDB is not available before version 2017, therefore not all of the scripts would work):

  1. 0000 CREATE DATABASE.sql
  2. 0100 Prepare POI.sql
  3. 0190 Add POI.sql
  4. 0300 Prepare Family.sql
  5. 0390 AddFamily.sql

For versions before SQL Server 2017, samples for routes and recipes are not available, as they depend on the new feature called “Graph Database”.

If you have version 2017 or newer installed, please execute the following scripts (you can execute all scripts as well without any harm, but it is just not necessary to execute scripts for the older SQL Server version, as everything is contained in the following scripts anyways):

  1. 0000 CREATE DATABASE.sql
  2. 0110 Prepare POI.sql
  3. 0190 Add POI.sql
  4. 0210 Prepare Route.sql
  5. 0290 AddRoute.sql
  6. 0310 Prepare Family.sql
  7. 0320 Prepare FamilyGraph.sql
  8. 0390 AddFamily.sql
  9. 0410 Prepare Recipe.sql
  10. 0490 AddRecipe.sql


Executing the scripts in this order will ensure, that you will have all necessary objects at hand, containing a clean state.


Now, go and play! 🙂


Yours sincerly,
Markus EhrenmĂĽller-Jensen







Welcome to my blog about how hierarchies and graphs can be modelled, queried, operationalized and tuned with SQL Server. This blog is accompanied by a github repository and talks and workshops I deliver(ed) at several conferences and occassions.

The basic idea for this blog goes way back to 2010, when I was creating an abstract for a talk about modelling hierarchies with different tools of Microsoft’s BI stack. I took a more naive approach back in those times (eg. with an abstract consisting of only two sentences!) and submitted a talk with the name “Luke I’m your father – Hierarchies in SQL Server” to SQLbits 2011. To my surprise (it was my first time that I submitted to a conference outside of Austria or Germany) the talk was chosen – and left me with the task to actually come up with a nice sample database for eg. a family tree of Luke’s family. I first than discovered, that George Lucas’ “Star Wars” universe does not document much of the families and I had to stick (again) to AdventureWorks demo database for my live-demos. (I than pimped up my slides with references to Star Wars instead. :-))

When some years later the bug of George R. R. Martin’s “A Song of Ice and Fire” has bitten when I saw the first episode of HBO’s tv-show “Game of Thrones” I immediately reminded myself of my talk about hierarchies in SQL Server. And it took me less than the time to read all available books (since the progress with the tv-show was way too slow in my opinion) before I refurbished the talk I had done for SQLbits with my very own sample database for “A Game of Hiearchies”.

And here we are!

I hope you will enjoy reading about (un)traditional modeling approaches in relational databases, hierarchieid, recursive common table expressions (CTE) and loops and, of course, SQL Server 2017’s new feature “Graph Database”.

Sincerly yours,
Markus EhrenmĂĽller-Jensen