Simple way to maintain 2 level hierarchical data in SQL Server

23. March 2015 12:53 by Parakh in SQL Server  //  Tags: , ,   //   Comments

Key takeaway:

Often we are required to maintain simple 2 level hierarchical data in databases. Such data is generally used to power dropdowns, radio button groups, checkboxes etc. where user input is required. This data is generally persisted in relational databases in separate tables which connect to the tables of interest via one to many relationships.

This method suggests a key-value like system to maintain such data, thereby reducing the total number of tables and at the same time maintaining flexibility to extend your data-model.

Read on:

I have been working on projects and have implemented a simple way to maintain simple 2 level hierarchical data. A lot of them were focused on replacing a Content Management System that has been in use since the last decade with small dedicated systems that are flexible and correspond to the changing business landscape. One concept that we borrowed from the legacy system was the system to maintain data that drive business logic in an application using key-value like 2 level hierarchical data. Since a CMS can be used to manage a variety of data that the company making the CMS cannot know about in advance, the pattern is used in such systems.

At the heart of the concept are two tables – one to store parent data and the other table that houses child data. Hence key-value like one to many hierarchy.

Let’s take an example:

We will be modeling the kinds of transports that exist and the corresponding vehicles.

Consider the following data-model for such a system:

Data model

Description: The data-model of the setup of key-value tables. Bold entries are required fields.

Key Table:

Column Name

Data type

Description

KeyID

Int (Primary key)

The auto incrementing int type primary key.

Key

Nvarchar(100)

The entry that denotes the parent.

Description

Nvarchar(1000)

Description about the parent.

ShortName

Nvarchar(50) (Unique)

A short abbreviation for the parent entry. This will be used in where clause in sql queries instead of primary key, as there’s a possibility of primary key changing when data is moved from one environment to another (e.g. dev to test). The short name is going to remain constant and unique

IsActive

Bit

Denotes whether the entry is active or not.

CreatedBy

Nvarchar(100)

Entry to denote who actually created the entry.

CreatedDate

DateTime

Entry denoting the date and time when the entry was created.

ModifiedBy

Nvarchar(100)

Entry to denote who modified the entry.

ModifiedDate

DateTime

Entry denoting the date and time when the entry was modified.

Value Table:

Column Name

Data type

Description

ValueID

Int (Primary key)

The auto incrementing int type primary key.

KeyID

Int (Foreign key)

Reference to the primary key ValueListID in ValueList table.

Sequence

Int

The sequence denotes the order in which the child entries should appear in a select statement. This is helpful in defining an order in case of dropdowns.

Value

Nvarchar(100)

The entry that denotes the child of the parent entry in ValueList table.

Description

Nvarchar(1000)

Description about the child entry.

ShortName

Nvarchar(50) (Unique)

A short abbreviation for the child entry. This will be used in where clause in sql queries instead of primary key, as there’s a possibility of primary key changing when data is moved from one environment to another (e.g. dev to test). The short name is going to remain constant and unique

IsActive

Bit

Denotes whether the entry is active or not.

CreatedBy

Nvarchar(100)

Entry to denote who actually created the entry.

CreatedDate

DateTime

Entry denoting the date and time when the entry was created.

ModifiedBy

Nvarchar(100)

Entry to denote who modified the entry.

ModifiedDate

DateTime

Entry denoting the date and time when the entry was modified.

As you can see from the data model, the parent entries are kept in the Key table and the child entries are kept in the Value table. The two tables are related via one to many relationship. The role of the ShortName field in both the tables is to help in the creation of sql queries that include the where clause based on which selection can be done. Often data is moved from one environment to other, and in the process it may happen that the primary key may change. This is especially true of databases where uniqueidentifier data type keys are used as surrogate primary keys, like data models of content management systems.

Now let’s create these tables and fill them up with some data.

Create Table dbo.[Key]
(
    KeyID Int identity(1,1) Primary key,
    [Key]    Nvarchar(100),
    Description    Nvarchar(1000),
    ShortName    Nvarchar(50) Unique,
    IsActive    Bit,
    CreatedBy    Nvarchar(100),
    CreatedDate    DateTime,
    ModifiedBy    Nvarchar(100),
    ModifiedDate    DateTime
);
Go
 
Create Table dbo.Value
(
    ValueID    Int Identity(1,1) Primary key,
    KeyID    Int,
    Sequence    Int,
    Value    Nvarchar(100),
    Description    Nvarchar(1000),
    ShortName    Nvarchar(50) Unique,
    IsActive    Bit,
    CreatedBy    Nvarchar(100),
    CreatedDate    DateTime,
    ModifiedBy    Nvarchar(100),
    ModifiedDate    DateTime
);
Go
 
Alter Table dbo.Value
Add Constraint FK_Value_Key Foreign Key (KeyId)
References dbo.[Key](KeyId);
Go
 
Create NonClustered Index NCI_Value_KeyId on dbo.Value(KeyId);
Go
 
Create NonClustered Index NCI_Value_ShortName on dbo.Value(ShortName);
Go
 
Insert into dbo.[Key] Values
('Ground Vehicles','Vehicles available for use on ground','GRND_VEH',1,'Parakh Singhal',GetDate(),null,null),
('Sea Vehicles','Vehicles available for use in sea','SEA_VEH',1,'Parakh Singhal',GetDate(),null,null),
('Air Vehicles','Vehicles available for use in air','AIR_VEH',1,'Parakh Singhal',GetDate(),null,null);
 
Insert into dbo.Value Values
(1,0,'Car','Car','GRND_VEH_CAR',1,'Parakh Singhal',GetDate(),null,null),
(1,0,'Truck','Truck','GRND_VEH_TRK',1,'Parakh Singhal',GetDate(),null,null),
(2,0,'Submarine','Submarine','SEA_VEH_SBM',1,'Parakh Singhal',GetDate(),null,null),
(2,0,'Ship','Ship','SEA_VEH_SHP',1,'Parakh Singhal',GetDate(),null,null),
(3,0,'Air Plane','Air Plane','AIR_VEH_PLN',1,'Parakh Singhal',GetDate(),null,null),
(3,0,'Hot Air Baloon','Hot Air Baloon','AIR_VEH_BLN',1,'Parakh Singhal',GetDate(),null,null);

 

Now suppose we have to query the different types of vehicles that available for use on ground, then we can run the following query:

SELECT dbo.Value.ValueID
      ,dbo.Value.KeyID
      ,dbo.Value.Sequence
      ,dbo.Value.Value
      ,dbo.Value.Description
      ,dbo.Value.ShortName
      ,dbo.Value.IsActive
      ,dbo.Value.CreatedBy
      ,dbo.Value.CreatedDate
      ,dbo.Value.ModifiedBy
      ,dbo.Value.ModifiedDate
  FROM dbo.Value Inner Join dbo.[Key]
on dbo.Value.KeyID = dbo.[Key].KeyID
where dbo.[Key].ShortName = 'GRND_VEH'

Summary:

Key-Value tables form a very concise way to store 2 level hierarchical data. The idea is primarily applied in case of content management systems where the product company cannot know in advance as what all categories will be created in the system. Using this concept you can get rid of a lot of tables that are just used to store ‘choice’ or ‘category’ data.

Month List

ParakhSinghal@Twitter.com

Note: For Customization and Configuration, CheckOut Recent Tweets Documentation