Modelling a simple sales application

Working with Data
1

I’m building an app which serves as kind of finances manager, right now it’s pretty basic, basically you can register clients and sales so currently I have 3 collections users, clients and sales:

  • A user can have many clients.
  • A client can have many sales.

Both clients and sales are modeled according to my app views, I did it this way to avoid using joins(lookups) as much as possible.

My schemas:

Sales:

{
  _id,
  userId,
  clientId,
  clientName,
  clientNameDetails,
  items,
  payments,
  totalSaleValue,
  totalPaidAmount,
  currentDebt,
}

Clients:

{
  _id,
  clientName,
  clientNameDetails,
  totalDebt, // stores the sum of currentDebt for all the Sales.
  totalSalesValue, // stores the sum of totalSaleValue for all the Sales.
}

Users:

{
  _id,
  username,
  totalOwedValue, // stores the sum of totalDebt for all the Clients.
  debtors, // stores the number of clients who currently owe a debt to the User.
}

So as you can see my collections share some fields (clientName for instace) and some fields depend on others, for instance:

  • totalDebt in Client depends on currentDebt in Sales.
  • totalOwedValue in Users which depends on totalDebt in Clients.

Therefore every time a user adds a new sale I not only have to perform a query to the Sales collection but also to the Clients and Users collections, so 3 queries for every request (which according to postman can take as long as 437ms and I’m not even using transactions) I think this is too much, specially for an app that is in a basic state right now.

So how could I model this entities to avoid the necessity of performing 3 queries in one request? or at least reduce it to 2 queries (since the query to Users is not avoidable in some situations).

  • The first option that came to my mind was to embed the Sales collection inside the Client collection, but then I’ll have to deal with nested arrays (the payments field in Sales is an array) every time I want to insert, update or delete a payment, and also my initial plan of modeling every collection based on the correspondent view to avoid using joins(lookup) when reading would no longer be possible, not to mention that I could face a max size issue since documents can only store 16mb of data.
  • The other options was to use Atlas triggers, and actually it helps for some of my endpoints but not for the majority of them.