这是将JSON存储在MySQL中的合理用例吗？ [英] Is this an reasonable use case for storing JSON in MySQL?

问题描述

我知道，由于事实变得难以维护，不容易搜索或被其他方式查询，所以将JSON存储在MySQL列中通常被认为是一个坏主意。但是，我觉得在我的应用程序中遇到的情况是在我的MySQL表中存储JSON数据的合理用例。我确实在寻找一个答案，特别是可以指出我可能忽视的任何困难，或者有什么好的理由来避免我的计划，如果是这样，另一种方法。

I understand that it is generally considered a "bad idea" to store JSON in a MySQL column due to the fact that it becomes difficult to maintain and is not easily searched, or otherwise queried. However, I feel that the scenario I have encountered in my application is a reasonable use case for storing JSON data in my MySQL table. I am indeed looking for an answer, particularly one that may point out any difficulties which I may have overlooked, or if there is any good reason to avoid what I have planned and if so, an alternate approach.

现在的应用程序提供资源和库存管理，并支持构建程序集，可能包含无限数量的子程序集。

The application at hand provides resource and inventory management, and supports building Assemblies, which may contain an infinite number of sub assemblies.

我有一个表格，其中包含项目的所有元数据，例如其名称，sku，零售价格，维度，最重要的是这个问题：项目类型。项目可以是部分或程序集。对于定义为程序集的项目，其内容存储在另一个表格 item_assembly_contents 中，其结构相当预期，使用 parent_id 列将子项链接到父项。如您所料，任何时候，用户可以决定从程序集中添加或删除某个项目，或者另行修改程序集内容或完全删除程序集。

I have a table which holds all of the metadata for items, such as their name, sku, retail price, dimensions, and most importantly to this question: the item type. An item can either be a part or an assembly. For items defined as assemblies, their contents are stored in another table, item_assembly_contents whose structure is rather expected, using a parent_id column to link the children to the parent. As you may expect, at any time, a user may decide to add or remove an item from an assembly, or otherwise modify the assembly contents or delete it entirely.

这里是上述表格描述的可视化表示，填充了当组合时的数据，创建一个包含另一个程序集的程序集。

通过上述结构，任何从项目表格也将在 item_assembly_contents 表中通过InnoDB自动删除 ON DELETE CASCADE 。

Here is a visual representation of the above table description, populated with data that when composed, creates an assembly containing another assembly. With the above structure, any item that is deleted from the items table will also be automatically deleted in the item_assembly_contents table via InnoDB ON DELETE CASCADE.

这是一个非常简单的示例，以JSON格式组装，展示了一个子组件结构。

Here is a very simple example Assembly in JSON format, demonstrating a single Sub Assembly structure.

{
   "id":1,
   "name":"Fruit Basket",
   "type":"assembly",
   "contents":[
      {
     "id":10,
     "parent_id":1,
     "name":"Apple",
     "type":"part",
     "quantity":1
      },
      {
     "id":11,
     "parent_id":1,
     "name":"Orange",
     "type":"part",
     "quantity":1
      },
      {
     "id":12,
     "parent_id":1,
     "name":"Bag-o-Grapes",
     "type":"assembly",
     "quantity":1,
     "contents":[
        {
           "id":100,
           "parent_id":12,
           "name":"Green Grape",
           "quanity":10,
           "type":"part"
        },
        {
           "id":101,
           "parent_id":12,
           "name":"Purple Grape",
           "quanity":10,
           "type":"part"
        }
     ]
      }
   ]
}

水果篮是一个大会，其中包含一个小组名为Bag o Grapes。这一切都很好奇，直到订单和货物考虑。

The Fruit Basket is an Assembly, which contains a Sub-Assembly named "Bag o Grapes". This all works wonderfully, until orders and shipments come into consideration.

例如，出货包含装配。在任何时候，用户必须能够看到装配的内容 ，因为它们在发货时定义 ，这排除了简单地从项目和 item_assembly_contents 表，因为这些表可能是自创建发货以来已被修改的。因此，组装内容必须与货物明确保存，以便在以后的日期查看，独立于用户定义的库存中的装配体的状态或仅存在（即， code> items table）。

Take for example, an outbound shipment containing an assembly. At any time, the user must be able to see the contents of the assembly, as they were defined at the time of shipment, which rules out simply retrieving the data from the items and item_assembly_contents table, as these tables may have been modified since the shipment was created. Therefore, assembly contents must be explicitly saved with the shipment so that they may be viewed at a later date, independent of the state or mere existence of the assembly in the user's defined inventory (that being, the items table).

它正在将装配内容沿着我有点困惑的装运内容，在我看来，将数据存储在JSON中是一个可见的解决方案。了解以下数据至关重要：

It is storing the assembly contents along side the shipment contents that has me a bit confused, and where it seems to me that storing the data in JSON is a visable solution. It is critical to understand the following points about this data:

1. 将不用于搜索
2. 
   
3. 任何更新只会覆盖该行的内容


4. 将使用 MOST OFTEN 填充客户端上的树视图，它将接受表中存在的JSON，而无需修改。

1. It will NOT be used for Searches
2. Any UPDATES will simply overwrite the contents of the row
3. It will MOST OFTEN be used to populate a Tree View on the Client, which will accept the JSON as it exists in the table, without any need for modification.

看到这个图像（希望）更清晰的数据可视化：

See this image for a (hopefully) more clear visualization of the data:

问题

1. 这是一个合理的用例吗？我的关心是无意义的吗？


2. 有没有什么可以回来咬我？


3. 你能提供一个解释，为什么我不应该继续我的提出的模式，如果是这样....


4. 你可以提供一种替代方法吗？

1. Is this a reasonable use case? Are my concerns meaningless?
2. Is there anything that I have looked over that may come back to bite me?
3. Can you provide an explanation why I should NOT proceed with my proposed schema, and if so....
4. Can you provide an alternative approach?

一如以往，非常感谢您的时间，请随时要求澄清或其他信息。

As always, thank you so much for your time and please do not hesitate to request clarification or additional information.

更新
根据 @Rowland Shaw 的建议（以下），我提出了另一个提出的表结构，使用反身或兔耳关系到order_assembly_contents表。请参阅以下图片：

UPDATE As per @Rowland Shaw's suggestion (below), I've come up with another proposed table structure using a reflexive or "bunny ear" relationship to the order_assembly_contents table. Please see the following image:

我觉得这比直接存储JSON更优雅，因为数据更关系和数据库友好。检索数据并形成客户端也应该很简单！请提供上述结构的任何输入！

I feel this is a lot more elegant than storing the JSON directly, as the data is more relational and database friendly. Retrieving the data and forming it for the client should be easy-peasy as well! Please provide any input on the above structure!

推荐答案

通常，对于订购系统，我会期待像

Typically, for an ordering system I'd expect something like

Product -< OrderLine >- Order

在您的情况下，您可以在产品上添加一个兔耳朵关系来引用自己。因此，您的 outbound_shipment_contents 将名称，类型 code>产品。然后，您可以根据需要递归地建立项目树。

In your case, you could add a "bunny ear" relation on your Product to refer to itself. So your outbound_shipment_contents loses name, type to the new product. You can then recursively build up the tree of items to pick as required.

这篇关于这是将JSON存储在MySQL中的合理用例吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！