MySQL：嵌入式JSON与表 [英] MySQL: Embedded JSON vs table

问题描述

我正在为视频制作项目管理应用程序设计数据库模式，并努力保持一些嵌入式但不可重复的数据。在我采用的几个CS课程中，关系数据库归一化的一部分是识别可重复的块并将其封装到自己的表中。如果我有一块嵌入/嵌套数据，我知道可能是独一无二的记录？

示例：A 视频 record有许多 shoot_locations 。这些位置很可能永远不会重复。 shoot_locations 也可以包含多个 shoot_times 。在JSON中表示，可能如下所示：

  {
 video：{
 shoot_locations：[
 {
名称：Bob's Pony Shack，
地址：99 Horseman Street，Anywhere，US 12345，
 shoot_times：{
 shoot_at：2015-08 -15 21:00:00，
 ... 
} 
}，
 {
名称：Jerry's Tackle，
地址： 15 Pike Place，Anywhere，US 12345，
 shoot_times：{
 shoot_at：2015-08-16 21:00:00
 ... 
} 
} 
]，
 ... 
} 
} 
  





选项...

1. 在JSON字段中存储 shoot_locations （在MySQL 5.7.8中可用）


2. 为数据创建一个单独的表。


3. 其他？

我觉得我应该将嵌入式数据分解为自己的表格，并为不重要的元数据保存JSON。

摘要

存储非重复嵌入式数据的最佳选择是什么？

解决方案

正常化数据库的一个原因是减少冗余（您的可重复块）

另一个原因是允许向后查询。如果您想知道哪个视频在15 Pike Place中被拍摄，您的JSON解决方案将失败（您必须诉诸连续阅读，解码JSON，从而破坏RDBMS的目的）

良好的经验法则：

• 结构化数据 - 放在表格和列中


• 可能是查询条件的一部分的数据 - 放在表格和列中


• 您认识的非结构化数据将无法通过BLOB，XML或JSON字段进行查询

如果有疑问，请使用表和列。你最初可能要花一些额外的时间，但你永远不会后悔的。人们后悔自己选择JSON字段（或XML，就此而言）一次又一次。我是否提到再次？

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?

解决方案

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

这篇关于MySQL：嵌入式JSON与表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！