Several old timers have come to me asking if putting lots of data in a JSON column is cheating? After all data normalization is part of relational databases and the way to efficiency and speed is well organized data. This higgledy–piggledy fashion of putting an entire document in a column without breaking it down to its component sections does violate the first rule of data normalization. And that has worked for decades pretty well.
But things change.
Many new development projects start with no idea what their data is going to look like. Maybe, if they are lucky, they will pick an API for interchanging data. Some will decide to use a database as a persistent message queue. Sometimes the service is product and the products offered by that service are nebulous at beast at the onset so the developers do not want to be locked into a schema.
And schema changes get expensive quickly. Code written before the change needs to be updated and you have to get the DBA to run an ALTER TABLE that can take too long to complete. And rolling back is almost impossible.
Compounding this is the lack of database skills in new developers. Very few have any training in Structured Query Language, relational theory, sets, data normalization, or other skills that have been held in high esteem since the days of Codd. And these new developers argue that they do not need these skills as technology and approaches to coding have evolved. So if the foundation of relational databases is the weak link then remove it.
The JSON data type has been very popular with developers. In the past you could dump JSON documents in a CHAR column and used REGEX or have your application dig out the parts you needed from within the document. Now with MySQL 5.7 there is a native JSON data type with supporting functions so that developers can manipulate the document data easily. So storing data in a JSON format in a column of a table within a database becomes a valuable has becomes a useful part of a developers life.
Ruby on Rails was the first piece of software that allowed many developers to see the power of CRUD (Create Read Update Delete) access to a database without needing to know SQL. The MySQL Document Store features that arrived with 5.7.13 also provide CRUD so developers can use 'collections' for schema-less data storage. The developers do not need to know SQL. relational theory, or any of the other old stuff. The good news for the old timers is that the data may be stored in a schema-less fashion but data is stored in a good ol' MySQL table. Which means the decades spent gathering SQL skills still work on this new data.
But is it cheating? Yes, and no. Yes in that you would get better performance out of having all your data in fourth or fifth normalized form just like a good driver can get better performance out of good driving practices with a manual transmission. No in that the data is still in a relational database and pertinent information can still be pried out of the JSON data. Heck you can even make materialized columns from the JSON data to create indexes.
