I've designed my app so it stores the data of each sale in the following way: each order contains different products so for each product in an order a table stores the quantity(of the product) , net sale and earnings.

I will need earnings and net sales of days and months for display, so my approach would be to sum the individuals earnings and nets of every product sold in a day, but I think that as data gets bigger it would get expensive on the app's performance so my question is: Should I make another table for net sales and earnings for each day and month?, so as time passes it might be more efficient. I'm using code-first database with .net core 2.1, junior developer working on a full-stack project


It's common to have a conflict of goals and access patterns; the sales need to be stored individually but for reporting a different format is needed for performance and other reasons. You are on the right track to have a copy stored for reporting purposes, but there's a couple solution alternatives to consider:

  • Storing the reporting data in a separate, "downstream" from the source of truth database
  • Using an reporting product or engine which does this implicitly. For example, if you were to use Microsoft's Analysis Services, you'd define a SUM(SalesAmount) Fact and a Date Dimension, and the Analysis service will build the sub-sums internally because it knows you will be querying SUM(SalesAmount) over Date.
  • Using a reporting product like PowerBI which does not need such pre-configuration but can still appropriately index your data

You should also think about data retention. Are the sales details really needed for all the years? Can they be generated live for the current year, but can the system just migrate the old sales data and just keep the sums for past years?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.