0

I have a following scenario and would like to seek for advices/recommendation about the best solution to implement it.

Assuming I have following data in the DB:

OrderID CustomerID OrderDate Prices
1 CustomerA 15-Jan-2022 30
2 CustomerA 15-Jan-2022 20
3 CustomerB 15-Jan-2022 13
1 CustomerB 16-Jan-2022 15

And would like to display following table on webpage:

CustomerID OrderDate TotalPrices Breakdown
CustomerA 15-Jan-2022 50 Click me
CustomerB 15-Jan-2022 13 Click me
CustomerB 16-Jan-2022 15 Click me

When user click on the "Click me" for customerA on 15-Jan-2022, it will shows following:

OrderID CustomerID OrderDate Prices
1 CustomerA 15-Jan-2022 30
2 CustomerA 15-Jan-2022 20

The current possible solutions in my mind are following:

  1. User SQL consolidate to DB -> Return the summary table to Java API -> Send the summary table to Frontend -> When user click on the "Click Me" button, will have another SQL call to retrieve the breakdown records.

  2. Retrieve the DB records -> Consolidate in Java API -> Send the summary table and breakdowns to frontend.

Feel free to suggest for more ideas on this, thanks!

Khai Zhan YUONG
  • 1
  • 2
  • Why wouldn't you just calculate those on the fly? You need two queries, one to return the overview results and one for the "click me" logic. – Kayaman May 27 '22 at 06:08
  • 1
    It's usually best to do data aggregation like this on the database rather than in Java whenever possible. – QuentinC May 27 '22 at 06:24
  • Hi @Kayaman, thanks for your reply, currently I'm using two queries for the overview and the breakdowns. Should I pass the breakdowns result together when retrieving the overview result? Or I should wait user click on the "Click Me" and then we only execute the second query to retrieve the breakdowns? – Khai Zhan YUONG May 27 '22 at 09:36
  • Hi @QuentinC, Thanks for your reply. Now I'm doing the data aggregation on the database level. I think it is quite efficient to aggregate on database if there is a lot of records included. :D – Khai Zhan YUONG May 27 '22 at 09:38
  • Why wouldn't you wait for the user to click on "click me"? What if the data changes? Are you going to return stale data? – Kayaman May 27 '22 at 10:44
  • I think it is quite efficient to aggregate on database if there is a lot of records included. ==> If you find it slow, you can store the total for the whole order when you create/update it. Caching data can be interesting as soon as you read a lot more frequently than you update it. – QuentinC May 27 '22 at 12:23

0 Answers0