20

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?

ToddSmithSalter
  • 715
  • 6
  • 20
  • Rarely, and only when it's a repeat customer. Most video shoots are in unique locations. – ToddSmithSalter Aug 12 '15 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 Aug 13 '15 at 00:37

1 Answers1

30

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

Hazzit
  • 6,782
  • 1
  • 27
  • 46
  • 1
    in 2019, is it still the case? I stumbled upon a great article on how to query JSON fields: https://scotch.io/tutorials/working-with-json-in-mysql What do you think about it? – Armand Jul 27 '19 at 17:34
  • 4
    @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. – Symphony0084 Jan 18 '20 at 12:33