MongoDB架构设计(嵌套数组与单独的集合) [英] MongoDB Schema Design (nested array vs separate collection)

问题描述

我正在编写客户端管理Web应用程序.我正在尝试找出管理客户付款关系的正确方法.一天一次的应用程序将请求发送到另一个API，并同步我存储在数据库中的每个客户的付款金额.我经常需要根据客户类型(contract_type，sale_date等)运行有关付款(付款金额)的报告.我已经有一个clients集合.我试图在两种模式之间进行选择:

I'm writing client management web application. I'm trying to figure out the right way to manage my clients-payments relations. Once a day application is sending request to another API and synchronizing amount of payments for each client which I store in my database. I constantly need to run reports on payments (amount of payments) based on type of client (contract_type, sale_date and so on). I already have a clients collection. I trying to choice between two schema:

{
      "client_id": "asdf123",
      "client_last_name": "BB",
      "address": "123 Main St",
      "city": "ATLANTA",
      "payments_history": [
        {
          "contract_number": "asdf123",
          "payment_date": ISODate("2012-09-02T07:00:00.0Z"),
          "amount": 103.33,
          "payment_number": NumberInt(1)
        },
        {
          "contract_number": "asdf123",
          "payment_date": ISODate("2012-09-30T07:00:00.0Z"),
          "amount": 103.33,
          "payment_number": NumberInt(2)
        },
        {
          "contract_number": "asdf123",
          "payment_date": ISODate("2012-11-04T07:00:00.0Z"),
          "amount": 103.33,
          "payment_number": NumberInt(3)
        }
      ]
  }

与创建单独的集合"payments"相对，其中每个文档都是payment.我觉得最好将这些数据分开，因为它将随每个查询将每个client文档扩展为大量数据(如果我选择特定的字段，它将仍然占用大量内存).但是另一方面，我将无法运行聚合报告(因为它基于来自两个不同集合的数据).最好的方法是什么?我应该将它们分开，并在服务器端(php)上使用两个不同的查询进行汇总吗?

Versus creating separate collection "payments", where each document is a payment . I feel that it is better to separate those kind of data, since it will grow every single client document to enormous amount of data with each query (which will still take a lot of memory if I'm choosing particular fields). But on the other hand I won't be able to run the aggregation reports ( since it based on data from two different collections). What is the best approach? Should I separate them and do aggregation with two different queries on server side (php)?

推荐答案

由于听起来您实际上需要查询客户上下文之外的付款数据(即汇总报告)，所以我不想添加每个单独的付款项目到客户收款对象.

Since it sounds like you will actually need to query against the payment data outside the context of the client (i.e. for aggregated reporting), I would not want to add each individual payment item to the client collection objects.

我当然会创建一个支付对象集合，然后在每个支付对象中引用客户对象中的支付密钥，并在支付对象中引用客户密钥，因此您有一种确定的方式来在任一方向上相互关联，或者有第三个集合将客户映射到付款.

I would certainly create a payment object collection, and then either reference a payment key in the client object for each payment and the client key in the payment object, so you have a definitive way to relate one to the other in either direction, or have a third collection mapping clients to payments.

这里最好的选择可能实际上取决于您的访问方式.例如，如果在需要建立关系的情况下查找总是在一个方向上进行，那么您可能甚至不需要在这两组对象上都使用这种外键".

What is preferable here may really depend on your access pattern. For example, you may not even need such "foreign keys" on both set of objects if the lookup is always going to be in one direction for cases where you need to establish the relation.

这篇关于MongoDB架构设计(嵌套数组与单独的集合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！