Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Embedded JSON vs table

I'm designing the database schema for a video production project management app and struggling with how to persist some embedded, but not repeatable data. In the few CS courses I took, part of normalizing a relational database was identifying repeatable blocks and encapsulating them into their own table. What if I have a block of embedded/nested data that I know is likely to be unique to the record?

Example: A video record has many shoot_locations. Those locations are most likely never to be repeated. shoot_locations can also contain multiple shoot_times. Representing this in JSON, might look like this:

{
  video: {
    shoot_locations: [
      {
        name: "Bob's Pony Shack",
        address: "99 Horseman Street, Anywhere, US 12345",
        shoot_times: {
          shoot_at: "2015-08-15 21:00:00",
          ...
        }
      },
      {
        name: "Jerry's Tackle",
        address: "15 Pike Place, Anywhere, US 12345",
        shoot_times: {
          shoot_at: "2015-08-16 21:00:00"
          ...
        }
      }
    ],
    ...
  }
}

Options...

  1. store the shoot_locations in a JSON field (available in MySQL 5.7.8?)
  2. create a separate table for the data.
  3. something else?

I get the sense I should split embedded data into it's own tables and save JSON for non-crucial meta data.

Summary

What's the best option to store non-repeating embedded data?

like image 584
ToddSmithSalter Avatar asked Aug 12 '15 17:08

ToddSmithSalter


People also ask

Is it good to store JSON in MySQL?

MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.

What is the drawback of JSON in MySQL?

The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.

What is the size of JSON in MySQL?

Contact MySQL | One of the more frequently asked questions about the native JSON data type, is what size can a JSON document be. The short answer is that the maximum size is 1GB.


1 Answers

ONE of the reasons of normalizing a database is to reduce redundancy (your "repeatable blocks")

ANOTHER reason is to allow "backwards" querying. If you wanted to know which video was shot at "15 Pike Place", your JSON solution will fail (you'll have to resort to sequential reading, decoding JSON which defeats the purpose of a RDBMS)

Good rules of thumb:

  • Structured data - put in tables and columns
  • Data that might be part of query conditions - put in tables and columns
  • Unstructured data you know you'll never query by - put into BLOBs, XML or JSON fields

If in doubt, use tables and columns. You might have to spend some extra time initially, but you will never regret it. People have regretted their choice for JSON fields (or XML, for that matter) again and again and again. Did I mention "again"?

like image 127
Hazzit Avatar answered Oct 14 '22 17:10

Hazzit