JSON Leads To New Wave Of 1NF Failures

Jovan Popovic talks about storing JSON in SQL Server:

Instead of single JSON object you can organize your data in this “collection”. If you do not want to explicitly check structure of each JSON column, you don’t need to add JSON check constraint on every column (in this example I have added CHECK constraint only on EmailAddresses column).

If you compare this structure to the standard NoSQL collection, you might notice that you will have faster access to strongly typed data (FirstName and LastName). Therefore, this solution is good choice for hybrid models where you can identify some information that are repeated across all objects, and other variable information can be stored as JSON. This way, you can combine flexibility and performance.

Okay, we’ve hit my first major problem with JSON support:  rampant violation of first normal form.  You can create check constraints on JSON code, and that’s pretty snazzy I guess, but I know a better way to store relational data in a relational database system.  JSON support is great when you ask SQL Server to be a holder of text blobs, but this is begging for bad design decisions.

Related Posts

HASHBYTES On FOR JSON PATH Data

Greg Low walks us through a mechanism to check whether data has changed: In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server. I later remembered that I’d seen a message by Adam […]

Read More

Flattening JSON Data With Databricks

Ivan Vazharov gives us a Databricks notebook to parse and flatten JSON using PySpark: With Databricks you get: An easy way to infer the JSON schema and avoid creating it manually Subtle changes in the JSON schema won’t break things The ability to explode nested lists into rows in a very easy way (see the […]

Read More

Categories

November 2015
MTWTFSS
« Jan Dec »
 1
2345678
9101112131415
16171819202122
23242526272829
30