MySQL Cookbook, 4th Edition

Chapter 19. Working with JSON

19.0 Introduction

Relational databases have proven to be effective for decades. They prevent duplicates and misses of data and enable fast access to stored values. However, business continually invents new scenarios in which data needs to be more flexible than the relational model allows.

For example, let’s consider a record for a user who can access subscription-only digital content and leave comments. For such a user, having only basic information—their name, email address, and password—is enough to get started. However, once the users start exploring more options (for example, requiring delivery), they may need their mailing address to be stored. The mailing address could be different from the billing address. The user may want to add a social network account or a few of them.

One way of storing flexible data in the relational database is to store additional pieces of data in the referenced table that shares details for each user. We discussed this technique in Recipes 16.5 and 16.6.

However, this technique may be not the best in the following situations:

When only a few items in the main table have details in the referenced table

If you still need to know about these details, when you query the required fields in the main table, you will need to join it with the referencing table every time. This will complicate queries and affect performance.

When most of the specific details could be missed

Details such as a user’s district or building number ...