Last week I wrote about SQL Server 2016. They are adding JSON as a data type. I’m not sure how much value that brings to a relational database engine. Certainly, the capability may be abused, as we have seen in XML. There are many comments in favor of a non-relational type of storage for the purposes of supporting Hierarchical data. Hierarchical data can be implemented in a fashion requiring many tables, or simply perform horribly. Many of us have experienced this issue. I thought I would touch on that particular issue with some options other than storing the data in SQL Server as XML or JSON.
The first thing that comes to mind is the ability to use multiple data stores. It is possible for an application to use multiple data stores. You could store your JSON or XML in a NoSql engine quite readily. I understand that not everyone has a NoSql engine selected, or the staff to support utilization and disaster recovery. Many businesses have policy blocking the user of anything other than traditional relational engines. So, this isn’t always a solution.
A second option is to learn to store hierarchical data in a relational database so that it can be modified and retrieved efficiently. Using a Type/Subtype data structure simplifies the hierarchy, because you can build the hierarchy on the type table, while using the Subtype tables to store completely different data. This is huge for building hierarchies containing many different kinds of entities relating to each other through a hierarchy rather than a simple parent/child foreign key.
A third option is to learn the Nested Set design, attributed to Joe Celko, as a method of organizing hierarchical relationships. This method is incredibly powerful. One of the biggest values is that an item in the hierarchy may have more than one parent without creating a complicated data structure. I demonstrate this in more detail at www.sqlsageadvice.com. You can also find more detailed examples with a google search on Nested Set.
Cheers,
Ben