Best practices using Auxiliar Collections

MongoDB Atlas App Services & Realm
1

Hi, I’m develpoing a POS system using realm. Of course this have sales, items, taxes, etc and by the type of system this would need a lot of Statistics to be shown.

My question is, would be recommended to use auxiliary collections to store the data in a way is would be optimized for collection that data?

For example, let’s say I have a Order->Item document relation. The items has a lot of fields, tax related, users, etc. If I need a report for hourly sales I would be benefited by having the date store as 3 string:
'month":“MM”, ‘day’:'DD, ‘hour’:‘HH’ instead of the classic bson type Date.

So I would have a collection with this document:

{
   item: 'T-shirt',
   quantity: x
   total: x
   year:
   month:
   hour: //
}

separated from the collection that has the actual order item data. Would be this a good practise? Considering that all those collections are read-only (once created, wouldn’t be modified).

Or it would be just better(or the same) to add those extra fields in the Item collection and querty directy in there?
Of course this would increase storage space but would I get some processing cpu/time benefit?

Any article about good practices would be welcomed.

thanks!

2

Hi @Mariano_Cano ,

There are several great articles on MongoDB schema design and if you are going to use offline sync with realm its best to consider realm and MongoDB side.

In general both sides will have similar best practices where 2 main rules apply:

  1. Data that is accessed together should be stored together.
  2. Duplicating immutable data is fine even on the cost of storage for better query pattern coverage.

In your case it sounds like having pre calculated summary table for product reports with deasambled index date parts might make sense.

I would recommend reading:

  1. A Summary of Schema Design Anti-Patterns and How to Spot Them | MongoDB
  2. MongoDB Schema Design Best Practices | MongoDB
  3. Building with Patterns: A Summary | MongoDB Blog

Thanks
Pavel

1 Like