A new approach to storing dynamic data in relational databases using JSON

JavaScript Object Notation was originally designed to transfer data; however, it soon found another use as a way to persist data in NoSQL databases. Recently, the most popular relational databases introduced JSON as native column type, which makes it easier to store and query dynamic database schema. In this paper, we review the currently popular techniques of storing data with a dynamic model with a large number of relationships between entities in relational databases. We focus on creating a simple dynamic schema with JSON in the most popular relational databases and we compare it with well-known EAV/CR data model and the document database. The results of precisely selected tests in the field of Criminal Data suggest that the use of JSON in dynamic database schema greatly simplifies queries and reduces their execution time compared to widely used approaches.

[1]  Malcolm K. Sparrow,et al.  The application of network analysis to criminal intelligence: An assessment of the prospects , 1991 .

[2]  Perry L. Miller,et al.  Research Paper: Exploring Performance Issues for a Clinical Database Organized Using an Entity-Attribute-Value Representation , 2000, J. Am. Medical Informatics Assoc..

[3]  Reuven M. Lerner.  At the forge: PostgreSQL, the NoSQL database , 2014 .

[4]  Jignesh M. Patel,et al.  Enabling JSON Document Stores in Relational Systems , 2013, WebDB.

[5]  Kristina Chodorow,et al.  MongoDB: The Definitive Guide , 2010 .

[6]  Daniel J. Abadi,et al.  Sinew: a SQL system for multi-structured data , 2014, SIGMOD Conference.

[7]  Doug McMahon,et al.  JSON data management: supporting schema-less development in RDBMS , 2014, SIGMOD Conference.

[8]  Guan Le,et al.  Survey on NoSQL database , 2011, 2011 6th International Conference on Pervasive Computing and Applications.

[9]  Adam Piórkowski,et al.  Productivity and nesting join the schemes and standarized and denormalized , 2010 .

[10]  Bruce Momjian,et al.  PostgreSQL: Introduction and Concepts , 2000 .

[11]  Jonathan Stern,et al.  Oracle Essentials: Oracle Database 10g , 2004 .

[12]  Marek Kisiel-Dorohinicki,et al.  Multi-Domain Data Integration for Criminal Intelligence , 2013, ICMMI.

[13]  Ying Liu,et al.  Closing the functional and Performance Gap between SQL and NoSQL , 2016, SIGMOD Conference.

[14]  Perry L. Miller,et al.  Application of Information Technology: Organization of Heterogeneous Scientific Data Using the EAV/CR Representation , 1999, J. Am. Medical Informatics Assoc..

[15]  황규영,et al.  Inverted index storage structure using subindexes and large objects for tight coupling of information retrieval with database management systems , 2002 .