MongoDB Schema Design (nested array vs separate collection) -
i'm writing client management web application. i'm trying figure out right way manage clients-payments relations. once day application sending request api , synchronizing amount of payments each client store in database. need run reports on payments (amount of payments) based on type of client (contract_type, sale_date , on). have clients collection. trying choice between 2 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) } ] } versus creating separate collection "payments", each document payment . feel better separate kind of data, since grow every single client document enormous amount of data each query (which still take lot of memory if i'm choosing particular fields). on other hand won't able run aggregation reports ( since based on data 2 different collections). best approach? should separate them , aggregation 2 different queries on server side (php)?
since sounds need query against payment data outside context of client (i.e. aggregated reporting), not want add each individual payment item client collection objects.
i create payment object collection, , either reference payment key in client object each payment , client key in payment object, have definitive way relate 1 other in either direction, or have third collection mapping clients payments.
what preferable here may depend on access pattern. example, may not need such "foreign keys" on both set of objects if lookup going in 1 direction cases need establish relation.
Comments
Post a Comment