3

MySQL supports JSON data type. Is it violating 1NF rule for atomicity of fields? If no then how is it justified?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Chinmaya B
  • 405
  • 1
  • 7
  • 21

2 Answers2

4

It depends on how the data is used within the rdbms, not just on the data type itself, whether any data (not just json) violates the 1NF. However, data type influences how often a data type is used to store data in a way that violates 1NF. Json being a complex data type with intrinsic structure, is very likely to be used in a way that violates 1NF.

Let me expand a bit what I mean by the use of the data as decisive factor in this question.

The wikipedia article on 1NF has a section on atomicity that describes the ambiguity around the interpretation of atomicity:

the notion of atomicity has no absolute meaning":[10] a value may be considered atomic for some purposes, but may be considered an assemblage of more basic elements for other purposes.

This means that if the particular json field is handled in an atomic way oblivious of its internal structure in the database layer, then it will not violate the 1NF.

However, if the database must be aware of the internal structure of a particular json field (e.g. search within it), then it violates 1NF.

To offer an analogue, strings stored in a varchar field would be considered atomic by most users (apart from zelous theorists). But if you store delimited values (or json or xml for that matter) and your database layer must be aware of such field's internal structure, then even a varchar field may violate the 1NF.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Zealous theorists would say "[atomic](http://stackoverflow.com/a/24038895/3404097)" is an unhelpful meaningless fuzzification, and that "1NF" isn't much better, because it is used to mean many different things, including some in terms of "atomic"; otherwise they would agree with you that any part of the database state that you want to quantify over should be an attribute. PS That wiki article is a mess. – philipxy Mar 06 '17 at 04:29
2

Yes it violates 1NF and 2NF (maybe the other Normal forms too, but I could never get my head round them).

But a relational DBMS is a very useful place to store small pieces of data - even as opaque blobs. It provides mechanisms for controlling concurrent access, authorization, replication, annotation...

Since JSON is simply a string with special formatting, nearly all DBMS will support it, MySQL simply provides limited scope for inspecting the content of the BLOB.

Chinmaya B
  • 405
  • 1
  • 7
  • 21
symcbean
  • 47,736
  • 6
  • 59
  • 94