具有共享数据的Mongodb数据库架构设计 [英] Mongodb database Schema Design with shared data

问题描述

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

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

我的关系表中有4个表Tenant，系统，授权.

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，租户C中选择A.Prop_Info，A.System_ID，其中A.System_ID = B.System_ID和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

从A，系统B中选择A.System_ID，A.Prop_Info 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?

我需要使用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和B的两个单独的查询返回相同的数据D(例如您的情况下为Prop_Info)，则必须在以下三种策略之间进行选择:

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中，并将引用(DBRef或其他一些标识字段的组合)存储在B中，以便您可以通过第二个查询来获取它.当对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，请使用数组.您可以为整个数组建立索引，并且可以使用 .

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为MongoDB的标记10gen工作.)

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

祝你好运！

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