Working with hierarchical data models

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

The hierarchical data model

Properties of hierarchical data models:

Represented as a tree structure
Has one root element
Each child record has one parent record

A possible representation of hierarchical data as a tree

Advantages:

Simple to understand
Fast to select

Disadvantages:

Rigidly constructed
Complicated to change structure

Example of hierarchical data model

Customer-bill-article relation:

One customer can have several bills and each bill can have several articles

CREATE TABLE Customer (   
    ID INT NOT NULL);
CREATE TABLE Bill (   
    BillID INT NOT NULL,  
      CustomerID INT);
CREATE TABLE Article (   
    ArticleID INT NOT NULL,  
      BillID INT);

Hierarchial representation of the customer-bill-article relation

The networked data model

Properties of networked data models:

Similar to hierarchical data models
many-to-many relation
Many search paths exists

A possible representation of networked data as a tree

Advantages:

No strict hierarchy
Many solution paths
Many real-world examples

Disadvantage:

Clarity decreases for large data models

Example of networked data models

Customer-order-article relation:

Many customers can have several orders and each order can have several articles.

CREATE TABLE Customer (   
    ID INT NOT NULL);
CREATE TABLE Order (   
    OrderID INT NOT NULL,  
      CustomerID INT);
CREATE TABLE Article (   
    ArticleID INT NOT NULL,  
      OrderID INT);

Networked representation of the customer-order-article relation

Let's practice!

Hierarchical and Recursive Queries in SQL Server