Mongodb 数据库模式设计与共享数据 [英] Mongodb database Schema Design with shared data

问题描述

我是 mongodb 的新手.我正在使用 java.

Hi I am newbie to mongodb.I am using java.

我的关系表中有 4 个表租户、系统、授权.

I have 4 tables Tenant,system,authorization in my relational table.

类似的东西.

Table            Fields

Tenant           Tenant_ID(PK), Tenant_INFO
System           System_ID(PK), System_Info
Authorization    System_ID, Autho_Info.
System_prop      System_ID, Prop_Info, Tenant_ID

在 System_prop 表中，Tenant_ID 指的是租户表 Tenant_ID (PK)，System_ID 指的是系统表 System_ID.

In System_prop table, Tenant_ID refers the Tenant Table Tenant_ID (PK), System_ID refers the System Table System_ID.

在授权表中，System_ID 指的是系统表 System_ID

In Authorization table, System_ID refers System tabel System_ID

我正在将我的数据库从关系型数据库切换到 mongodb.我需要做的第一件事是架构设计.

I am switching my database from relational to mongodb. First thing I need to do is Schema design.

我需要做的查询是:

1. 从 System_prop A、SYSTEM B、TENANT C 中选择 A.Prop_Info、A.System_ID 其中 A.System_ID = B.System_ID AND A.Tenant_ID = C.Tenant_ID

1. SELECT A.Prop_Info, A.System_ID From System_prop A, SYSTEM B, TENANT C Where A.System_ID = B.System_ID AND A.Tenant_ID = C.Tenant_ID

SELECT A.System_ID, A.Prop_Info FROM Authorization A, SYSTEM B WHERE A.System_ID = B.System_ID

SELECT A.System_ID, A.Prop_Info FROM Authoization A, SYSTEM B WHERE A.System_ID = B.System_ID

谁能帮助我如何将这些表设计为 mongodb 中的集合?

Can anyone help me how to design these tables as collections in mongodb?

我需要嵌入 r use dbref 吗?帮助我为此设计架构.

Do i need to embed r use dbref? Help me to design the schema for this.

推荐答案

您的挑战来自于两个查询都必须检索 Prop_Info 的事实.这使得很难确定它应该存放在哪个 Mongo 集合中.

Your challenge comes from the fact that Prop_Info must be retrieved by both queries. This makes it difficult to figure out which Mongo collection it should live in.

在 MongoDB 中，您创建文档架构的理想目标是让单个文档拥有给定查询模式所需的所有信息.如果您需要对两个单独的集合 A 的两个单独的查询返回相同的数据 D(例如您的案例中的 Prop_Info)> 和 B，你必须在以下三种策略中进行选择:

In MongoDB, you create your document schema with the ideal goal for a single document to have all the information you need given your query patterns. In the case where you need to have the same data D (such as Prop_Info in your case) returned by two separate queries against two separate collections A and B, you have to choose between the following three strategies:

1. 在A 和B 的文档中复制D，并强制与您的代码保持一致.这通常是高性能系统的设计选择，它们希望消除对第二次查询的需求，即使这是以插入/更新端的额外代码复杂性为代价的，并且由于 Mongo 不是 ACID，因此存在一些潜在的一致性问题.

1. Duplicate D in the documents of both A and B, and enforce consistency with your code. This is typically the design choice of high-performance systems that want to eliminate the need for a second query even if that comes at the cost of additional code complexity on the insert/update side and with some potential consistency problems since Mongo is not ACID.

将 D 放入 A 并在 B 中存储一个引用(DBRef 或某些其他标识字段的组合)，以便您可以通过第二个查询找到它.当对A 的查询数量超过对B 的查询数量时，这通常是设计选择.它将 D 保持在更频繁查询的集合的本地.在这种模式设计模式中，您只需要在查询 B 时进行第二次查询.

Put D in A and store a reference (DBRef or some other combination of identifying fields) in B so that you can get to it with a second query. This is typically the design choice when the number of queries to A exceeds the number of queries to B. It keeps D local to the more frequently queried collection. In this schema design pattern you only need to make a second query when you query B.

将 D 放入一个新集合 C 中，然后从 A 和 B<对它进行第二次查询/代码>.这通常是面对非常不确定的未来需求时的设计选择，如果您采用上述 (1) 或 (2)，则不清楚权衡是什么.它是最类似关系"的模式，当您同时查询 A 和 B 时，它会迫使您进行第二次查询.

Put D in a new collection C and make a second query to it from both A and B. This is typically the design choice in the face of very uncertain future requirements where it is not clear what the trade-offs would be if you go with (1) or (2) above. It is the most "relational-like" schema and the one that will force you to make a second query when you query both A and B.

您选择哪种策略取决于您的领域、查询模式、您从对象关系映射 (ORM) 框架中获得的支持(如果您使用一个)，以及最后但并非最不重要的，您的偏好.

Which strategy you choose depends on your domain, the query patterns, the support you get from your object-relational mapping (ORM) framework (if you use one), and last but not least, your preference.

在我遇到的情况下，我从未选择 (3).我在高性能情况下(分析系统)使用了 (1).我在其他任何地方都使用过 (2)，因为查询访问模式已经使共享"数据应该存在的位置变得很明显.

In the situations I've encountered, I've never chosen (3). I've used (1) in high-performance situations (analytics systems). I've used (2) everywhere else since the query access patterns have made it obvious where the "shared" data should live.

选择策略后，如果您仍然需要帮助，请发布另一个 SO 问题，专门针对给定策略的架构设计问题.

Once you pick your strategy, if you still need assistance, post another SO question that specifically focuses on the schema design problem given the chosen strategy.

最后三个提示:

1. 如果共享数据 D 的关系重数大于 1，则使用数组.您可以索引整个数组，并且可以使用 $elemMatch.

1. If the shared data D has a relationship multiplicity greater than 1 use an array. You can index entire arrays and you can query precisely inside arrays using $elemMatch.

要更新策略 (1) 或 (2) 中的 D，请使用 MongoDB 的 原子修饰符操作，其中许多旨在对数组进行操作.

To update D in strategy (1) or (2) use MongoDB's atomic modifier operations, many of which are designed to operate on arrays.

这个 SO 问题涵盖了 DBRef 中的两个查询模式@Stennie 的回答.(@Stennie 适用于 10gen，MongoDB 的标记.)

This SO question covers the DBRef two query pattern in @Stennie's answer. (@Stennie works for 10gen, the markers of MongoDB.)

祝你好运！

这篇关于Mongodb 数据库模式设计与共享数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！