Published
Mar 23 2019 06:24 PM
713
Views
First published on MSDN on Sep 07, 2016
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.
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.