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

Mateusz Piech; Robert Marcjan

doi:10.7494/csci.2018.19.1.2505

Authors

Mateusz Piech AGH University of Science and Technology
Robert Marcjan AGH University of Science and Technology

DOI:

https://doi.org/10.7494/csci.2018.19.1.2505

Keywords:

JSON, Relation Databases, EAV, Criminal Data, PostgreSQL

Abstract

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.

Downloads

Download data is not yet available.

References

DB-Engines Ranking. http://db-engines.com/en/ranking. Accessed: 201704-01. A new approach to storing dynamic data in relational databases using JSON

JSON in Oracle Database. https://docs.oracle.com/database/121/ADXDB/json.htm. Accessed: 2017-04-01.

Microsoft SQL Server End-User License Agreement. http://contracts.onecle. com/aristotle-international/microsoft-eula.shtml. Accessed: 2017-0401.

MySQL - The JSON Data Type. https://dev.mysql.com/doc/refman/5.7/en/json.html. Accessed: 2017-04-01.

Oracle Technology Network License Agreement. http://www.oracle.com/technetwork/licenses/standard-license-152015.html. Accessed: 2017-0401.

PostgreSQL - JSON Types. https://www.postgresql.org/docs/9.6/static/datatype-json.html. Accessed: 2017-04-01.

Chasseur C., Li Y., Patel J.M.: Enabling JSON Document Stores in Relational Systems. In: WebDB, vol. 13, pp. 14–15. 2013.

Chen H.: Javascript object notation schema definition language, 2014. URL https://www.google.com/patents/US20140067866. US Patent App. 13/596,694.

Chen R.S., Nadkarni P., Marenco L., Levin F., Erdos J., Miller P.L.: Exploring performance issues for a clinical database organized using an entity-attributevalue representation. In: Journal of the American Medical Informatics Association, vol. 7(5), pp. 475–487, 2000.

Chodorow K.: MongoDB: the definitive guide. " O’Reilly Media, Inc.", 2013.

Dajda J., Dębski R., Kisiel-Dorohinicki M., Piętak K.: Multi-domain data integration for criminal intelligence. In: Man-Machine Interactions 3, pp. 345–352. Springer, 2014.

Gray J.: Microsoft SQL Server, 1997. URL https://www.microsoft.com/en-us/research/publication/microsoft-sql-server/.

Greenwald R., Stackowiak R., Stern J.: Oracle essentials: Oracle database 12c. " O’Reilly Media, Inc.", 2013.

Han J., Haihong E., Le G., Du J.: Survey on NoSQL database. In: Pervasive computing and applications (ICPCA), 2011 6th international conference on, pp. 363–366. IEEE, 2011.

Jajeśnica Ł., Piórkowski A.: Productivity and nesting join the schemes and standarized and denormalized. In: Studia Informatica, vol. 31(2A), pp. 445–456, 2010.

Lerner R.M.: At the forge: PostgreSQL, the NoSQL database. In: Linux Journal, vol. 2014(247), p. 5, 2014.

Lim N.H.: PostgreSQL [9.5.0] vs MariaDB [10.1.11] vs MySQL [5.7.0] year 2016. http://nghenglim.github.io/PostgreSQL-9.5.0-vs-MariaDB-10.1. 11-vs-MySQL-5.7.0-year-2016/. Accessed: 2017-04-01. 22 Mateusz Piech, Robert Marcjan

Liu Z.H., Hammerschmidt B., McMahon D.: JSON data management: supporting schema-less development in RDBMS. In: Proceedings of the 2014 ACM SIGMOD international conference on Management of data, pp. 1247–1258. ACM, 2014.

Liu Z.H., Hammerschmidt B., McMahon D., Liu Y., Chang H.J.: Closing the functional and Performance Gap between SQL and NoSQL. In: Proceedings of the 2016 International Conference on Management of Data, pp. 227–238. ACM, 2016.

Momjian B.: PostgreSQL: introduction and concepts, vol. 192. Addison-Wesley New York, 2001.

MySQL A.: MySQL, 2001.

Nadkarni P.M., Marenco L., Chen R., Skoufos E., Shepherd G., Miller P.: Organization of heterogeneous scientific data using the EAV/CR representation. In: Journal of the American Medical Informatics Association, vol. 6(6), pp. 478–493, 1999.

Popovic J.: JSON Support in SQL Server 2016. https://blogs.msdn. microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/. Accessed: 2017-04-01.

Sparrow M.K.: The application of network analysis to criminal intelligence: An assessment of the prospects. In: Social networks, vol. 13(3), pp. 251–274, 1991.

Tahara D., Diamond T., Abadi D.J.: Sinew: a SQL system for multi-structured data. In: Proceedings of the 2014 ACM SIGMOD international conference on Management of data, pp. 815–826. ACM, 2014.

Whang K.Y., Park B.K., Han W.S., Lee Y.K.: Inverted index storage structure using subindexes and large objects for tight coupling of information retrieval with database management systems, 2002. US Patent 6,349,308.

Whittaker G.L.S.T.J.: Improving performance of schemaless document storage in PostgreSQL using BSON. In: , 2013.