When SQL Meets NoSQL

When you’ve got a project running for a long time, you’re likely running MariaDB as your database engine. Reasons are simple: You cannot use MySQL due to its GPL license and MariaDB is the true free version of MySQL.

Sooner or later you’ll probably think of something called “NoSQL”. It’s a useful technology and it’s often used in the design lately. For example, your e-commerce website needs to store many products to be sold. Each product may have common attributes, e.g. Name, Price, Quantity. But they have specific attributes, too. A computer has special attributes like RAM, Processor, Storage, while a book has different special attributes like Publisher, ISBN, Language, etc.

How should we store these specific attributes in our relational database then?

Of course, there’re solutions in the relational database world. EAV (Entity Attribute Values) Pattern can be used here to solve the problem. We can use table 1 to host all common attributes. We can then use table 2 to store specific attributes. This way we can combine the two tables by SKU the key.

Table 1

Table 2

This design is feasible but not efficient:

1. One more table to worry about
2. We’ll end up with maybe too many records in Table 2
3. SQL query to flatten the table is complex

At this point, you may wonder why not to use NoSQL. Why not migrate to MongoDB directly?

No, you can’t because the migration is time-consuming and risky as too many places to change and too many features waiting for you to implement. That’s quite disappointing, right? You know you can use NoSQL to solve your problem but just can’t do it now. Do we have a way out of this?

Yes, there is always a solution to a problem. The solution is your MariaDB. You know what, MariaDB supports NoSQL (JSON documents) since 10.2.x. Sometimes you may wonder: I should have read MariaDB’s release notes years ago :-)

MariaDB has several advantages than the traditional NoSQL databases:

1. You got the goodies of both SQL and NoSQL in one database
2. Query JSON documents using standard SQL statements
3. You can use transactions to modify multiple JSON documents

Let’s get a taste of how this works.

SELECT Name,
   JSON_VALUE(Attr, "$.RAM") AS RAM,
   JSON_VALUE(Attr, "$.Processor") AS Processor,
   JSON_VALUE(Attr, "$.Storage") AS Storage
FROM table2
WHERE  SKU = "1"

I’ll write another blog post to detail how to do the CRUD (Create, Read, Update, Delete) with JSON documents in MariaDB. Stay tuned!