Implementing Product Catalogs in SQL Server and Azure SQL database
Product catalog is one of the key scenarios in NoSQL systems. In product catalog scenario, you need to store different types of products with different properties (e.g. phones have memory and CPU power; cars have number of doors and max speed, etc.)
If you try to model this in relational database you will end-up either with single product table with a lot of sparse columns where 5-10 columns will be used for particular product types, or you will have a lot of tables (one for each product type) and joins with many tables that contain parts of data.
In NoSQL systems you can model product as JSON documents and put only required key:value pairs in product objects. However, accessing key:value fields is slower than accessing columns directly.
SQL Server 2016 and Azure SQL Database where new JSON support is available, enable you to combine best practices from relational and NoSQL models. You can store some common properties as standard table columns, put properties that are specific to some products in JSON documents, choose should you store some fields as JSON collections or separate tables (e.g. tags, comments, etc.)
If you want to see how to use this hybrid approach to design and index products in SQL server 2016 and Azure SQL Database, you can see following blog posts:
There are also sample apps that show you how to implement product catalog in SQL Server or Azure SQL Database on SQL Server GitHub JSON samples. One of the app is ASP.NET Core REST Service that exposes products, and another is ASP.NET Core Web application that displays products in catalog and uses JSON functionalities in SQL Server 2016 and Azure SQL Database.
Finally there is a new video on Channel9 where it is explained how you can model products by combining relational and JSON data, and also query or update JSON fields:
Using JSON in SQL Server 2016 and Azure SQL Database | Data Exposed
If you need to design product catalogs and if you want to use hybrid approach with SQL+JSON these might be helpful resources and code samples.
Comments
- Anonymous
October 02, 2016
Jovan, thanks for sharing this great overview and links to detailed examples of the new SQL Server JSON functionality!I was able to download and run the examples easily using an Azure SQL database.Have you ever shared any information / posts regarding the Belgrade.SqlClient project?I found the GIT repo and am trying to digest the code but I'm left wondering what the Belgrade.SqlClient really provides. Is that a nice to have or required to use SQL Server JSON functionality?