MongoDB - What's the best practice to store sub documents?-CodePudding

I'm new to MongoDB. I have a question regarding best practice for storing sub documents. Let's say that I have a User model. And then later on, I need to add payment history (1 user can have multiple payment history). In the terms of performance, which one would be better?

Adding payment history data directly to user model

User model:

{
  '_id' : 1,
  'name' : 'John',
  'payment_history' : [
    {
      'payment_date' : '2020-05-15T00:00:00Z',
      'desc' : '30-day Sub',
      'payment_method' : 'Paypal'
    }
  ]
}

Make a separate payment history model, user model saving only the payment id

User model:

{
  '_id' : 1,
  'name' : 'John',
  'payment_history' : [
      12345
  ]
}

Payment history model:

{
   '_payment_id' : 12345,
   'payment_date' : '2020-05-15T00:00:00Z',
   'desc' : '30-day Sub',
   'payment_method' : 'Paypal'
}

Thank you.

CodePudding user response：

data

db={
  "user": [
    {
      "_id": 1,
      "name": "John"
    }
  ],
  "payment": [
    {
      "_payment_id": 12345,
      "_user_id": 1,
      "payment_date": "2020-05-15T00:00:00Z",
      "desc": "30-day Sub",
      "payment_method": "Paypal"
    }
  ]
}

aggregate

db.user.aggregate([
  {
    "$lookup": {
      "from": "payment",
      "localField": "_id",
      "foreignField": "_user_id",
      "as": "payment_docs"
    }
  }
])

mongoplayground

CodePudding user response：

You have to define what performance means for your app.
If the most frequent or critical query needs User Payments then you better embed some payment information, just the fields needed to avoid $lookup or extra queries, and usually it makes sense to store only the last N payments.