ThinkingCog

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:

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

Key Table:

Value Table:

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.