Identify "best bang for the buck" items out of the results

Question

I need your help guys as I am stuck with this one.

How can I out of the below results pro-grammatically identify and print only 1(one) "best bang for the buck" record per each category? Taking into account unit price, operational cost and payback time.

Is there a universal formula for this?

I have a piece of Java code that gets some data from database with a few calculations and produces the results below.

ABProduct abproduct = null;
ArrayList result_new = new ArrayList();
result_new.addAll(ProductDao.getProdutcs(customer, country));

if(result_new!=null){
for(int i=0; i<result_new.size(); i++) {
    abproduct = (ABProduct) result_new.get(i);
    aID = abproduct.getID();
    aName = abproduct.getName();
    aPrice = abproduct.getPrice();
    aCategory = abproduct.getCategory();

    //The below variable are calculated on fly
    aCost = ...
    aPaybackTime = ...

    //Here I print the results
  }
}

Result looks like this:

ID  Name     Unit Price  Category  Operating Cost   Payback Time

11  AA1CC0   88.41       3         146.5            4.6
9   AA22VB   64.99       3         146.94           1.9
10  AA3BN5   79.69       3         147.51           4.1
18  AA47VV   88.08       3         147.63           5.3
17  AA5HJ2   79.32       3         149.55           5.6
16  AA61YA   75.24       3         150.16           5.4

21  AA9Y0A   104.99      5         140.74           3.9
33  AA10DA   182.03      5         140.74           9.4
40  AA11K1   119.99      5         140.82           5
32  AA121D   154.99      5         141.25           7.7
27  AA13S1   159.89      5         141.8            8.5
38  AA1433   102.05      5         141.93           4
41  AA15D0   139.99      5         141.97           7
31  AA166D   109.89      5         142.07           4.7
36  AA17D3   149.99      5         142.07           7.9
30  AA187H   99.99       5         142.17           4
35  AA19G5   129.99      5         142.17           6.3
22  AA20P4   119.99      5         142.39           5.6
39  AA21K8   109.95      5         142.52           4.9
20  AA228I   88.84       5         142.81           3.2
29  AA239O   92.47       5         142.96           3.6
24  AA241C   102.5       5         143              4.4
19  AA259L   73.99       5         143.08           2
23  AA260K   79.99       5         143.13           2.6
26  AA277B   84.99       5         143.35           3.1
34  AA28YI   305.99      5         144.51           24.9

42  AA31Y0   189.99      6         139.47           9.1
45  AA32Y9   138.14      6         139.54           5.8
44  AA33R7   129.99      6         140              5.4
46  AA34R4   203.11      6         140.25           10.5
43  AA35A3   99.99       6         140.37           3.5
47  AA36A4   229.99      6         140.38           12.5
48  AA37Q5   341.99      6         140.57           20.5

50  AA40DF   199.99      7         136.44           8.2
52  AA41CC   402.95      7         136.88           19.7
51  AA42VG   279.99      7         137.73           13.5
49  AA43EW   195.33      7         137.97           8.6

What I need it to be is (I do not know if I identified the right item from category 5):

ID  Name     Unit Price  Category  Operating Cost   Payback Time

 9  AA22VB   64.99       3         146.94           1.9
20  AA228I   88.84       5         142.81           3.2
43  AA35A3   99.99       6         140.37           3.5
50  AA40DF   199.99      7         136.44           8.2

Answer 1

So there are a few options here. The most straightforward option, which also applies in situations where you're not using a database, is to just separate everything by category and then sort. A map makes sense for this, e.g.:

• Go through each product and place into a map of category → products (e.g. a Map<Integer,List<Product>> or e.g. a Multimap from Guava or whatever - an ArrayListMultimap would be particularly well-suited since it returns conveniently modifiable value Lists that you can sort directly; another alternative is a TreeMultimap, which allows you to specify a value comparator on creation, and the products will just naturally end up sorted).
• Go through each category in that map, sort the list of products in that category (see Collections.sort and custom comparators), pull the first item from each list, and that's your results.

You'd have to have a way of making the calculated payback time values available to the comparator, for which you have a few options:

• Just do the payback time calculations in the comparator per comparison.
• Better option: Add a getPaybackTime() method to ABProduct and just do the calculations there. That way your calculations are in one reasonable place and you can access them anywhere you need them, e.g. in a comparator when sorting product lists.

Here's a quick and dirty example.

The second option is to use the query building capabilities of whatever O[RM / database API you are using to do this all in a SQL query instead. I won't get into the details here because "how to select the minimum value over groups" is a pretty common question for which there are various techniques (see SQL: Group by minimum value in one field while selecting distinct rows for example).

The way to make that work for you is to do the "payback time" calculation math in the SQL query itself, and to specify whatever query you come up with to whatever ORM / database API you are using (which you don't specify, and is beyond the scope of that answer), noting that you can do things like this with SQL (made up math):

SELECT 
   *,
   UnitPrice - OperatingCost AS Whatever
FROM
   Products

The downside here is now you've got your math in your SQL query when it's arguably a business function not a data access function. To make this at least somewhat manageable all around you'd probably want to at least create a view with this math if your database supports it (MySQL, for example), and possibly add the calculated column as a field in your entity class.

That second SQL-based option can get a bit unwieldy, so I'd just go with the first business-side approach (separate products by category, sort, assemble results) to start. Philosophically, it's debatably more appropriate anyways: This is a business-level operation, it's a bit cleaner to do it on the business side.

This answer isn't entirely complete, but I hope it helps.

---

By the way, for the first option (organize then sort), what you might actually want to do is add a "category" entity to your data layer as well, assuming you have a categories table, and set up a proper one-to-many category-to-product relationship.

Then you can have a dao method to get all the categories. That way you can query the list of categories, then go through each and directly grab the products in that category and sort (on the business side). Making a getPaybackTime() method in your product entity to do the calculations will help you here as well.

If you do it like this, you can skip the divide-into-categories step, since you'll be able to do it directly through the data layer now. This will have some performance and memory usage benefits as well.

This is the way I would choose to implement it, personally.