Configurable Application Development using MySQL-JSON
Now a days NoSQL databases are getting popular due to it’s flexibility of schema-less design. Relational database systems such as MySQL and PostgreSQL have started supporting storing and indexing native JSON data type to achieve the benefits of schema-less design.
Recently I got a requirement to design a configurable application and I tried this feature in MySQL and it’s quite amazing. Instead of a fully relational or non-relational model, this enables a hybrid model to achieve both features. So in this article I’m gonna explain how we can use JSON columns to build a flexible application while achieving the benefits of NoSQL.
Problem Statement
Lets say we are developing an application which consists of following three major operations.
- Eligibility/Validation management
- Payment management
- Subscription management
Based on the user input data the sub-operations under above major operations will be different. As an example for some users there will be several validation levels with different methods while for some there will be only one. Same thing can be happened for the subscription and payment (different sub-levels and different methods).
Therefore when new changes come or modifications has to be done it will be much difficult since we have to change several places since there is no centralized location to smoothly change the flow in dynamic way. So we’ll go for a configuration level solution for this such as we’ll keep the operation related data in a database and based on those data the user journey will be defined in the code level. If we want to introduce a new data type or modify a operation we can easily do the change in db level. The code level changes are obvious, however using this solution we’ll be able to reduce the effort and dynamically change the flow.
Tech stack : MySQL 5.7 or above, Java, Spring Boot
Conventional solution
Lets design a relational database model for above mention configurable solution.
This table contains the details related with users and in meta data section there can be type specific data which will be useful for operations.
Set of validations that need to be perform, for a given user there can be one or more validation levels.
This will bind the relevant user type with set of validation level. As an example user type of Prepaid has to be validated against Balance, status and Blacklist. The execution order will defined by using priority level.
As you can see there are multiple tables so far and the schema is already fixed. If we want to add an additional attribute for an operation that will be impossible since it change the whole table structure. We can scale the table vertically without any issue and there will be many records since there are many combinations.
This relational model get more complex when there are many operational levels and operation methods. Therefore lets try the same approach with in more flexible way using MySQL JSON data type.
Flexible Solution
Lets design a JSON data model for the above system.
Now we’ll see how to design this entity model using Java + Spring Boot application. Java representation for this model is shown below.
we need an Object Converter to do the serialization and deserialization.
The user attributes data can be validated and according to the available data the flow of the application can be defined. Here its more convenient to use a single table and we can use MySQL inbuilt functions to query the data.
Conclusion
There are both pros and cons of using this kind of hybrid model, but depends on your application you have to select the optimum solution.
JSON datatype columns use LONGTEXT /LONGBLOB or some form of CLOB/BLOB. Before using JSON datatype columns read the respective database documentation to understand and do performance testing. Limit the size of JSON data in these columns where ever possible.
PS : Since Relational Databases are built on the base of Normalization don’t try to use JSON column to replace all One To Many relationships.
So, try this method, and share your experience through comments. Till then, stay safe and happy coding!
