MySQL: Embedded JSON vs table

Question

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...

store the shoot_locations in a JSON field (available in MySQL 5.7.8?)
create a separate table for the data.
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?

Rarely, and only when it's a repeat customer. Most video shoots are in unique locations. — ToddSmithSalter
– ToddSmithSalter, Commented Aug 12, 2015 at 18:43
Hazzit has the right answer below. Don't think of locations as a header table in a header-detail relationship, it's just a 1..n attribute of a video. If you ever want to query locations, and you will since you are storing it in a DB, then put them in a locations table. — WoMo
– WoMo, Commented Aug 13, 2015 at 0:37

Hazzit · Accepted Answer · 2015-08-13 00:33:53Z

35

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"?

answered Aug 13, 2015 at 0:33

Hazzit

6,8921 gold badge29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Armand Over a year ago

in 2019, is it still the case? I stumbled upon a great article on how to query JSON fields: scotch.io/tutorials/working-with-json-in-mysql What do you think about it?

Symphony0084 Over a year ago

@Armand yes it is still the case. not much has changed in RDBMS design in years. Parsing an entire column of JSONs is always feasible, but will never be as structured or efficient as assigning the data to its own table.

Collectives™ on Stack Overflow

MySQL: Embedded JSON vs table

Options...

Summary

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Options...

Summary

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related