MySQL: embedded JSON vs. table

Question

I am developing a database schema for a video production project management application and struggling with how to preserve some embedded but not repeatable data. In several CS courses that I took, part of normalizing the relational database was identifying duplicate blocks and encapsulating them in their own table. What should I do if I have a block of embedded / nested data, which, as I know, can be unique for writing?

Example. There are many shoot_locations video shoot_locations . These places are likely to never be repeated. shoot_locations may also contain multiple shoot_times . A representation of this in JSON might look like this:

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

Options...

• save shoot_locations in JSON field (available in MySQL 5.7.8?)
• Create a separate table for the data.
• something else?

It seems to me that I should split the embedded data into my own tables and save JSON for non-critical metadata.

Summary

What is the best option for storing non-duplicate embedded data?

Answer 1

One reason for normalizing the database is to reduce redundancy (your “repeatable blocks”)

Another reason is to allow a “reverse” request. If you want to know what video was shot at "15 Pike Place", your JSON solution will fail (you will have to resort to sequential reading, decoding JSON, which defeats the purpose of the RDBMS).

Good rules of thumb:

• Structured data - put in tables and columns
• Data that may be part of the query conditions - put in tables and columns
• Unstructured data, which, as you know, will never be requested by entering BLOB, XML or JSON fields.

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