0

Let's assume the following structure. For each pair of item and category we have a rating (between 0 and 10)

item_id | category_id | rating
--------+-------------+-------
21      |          31 |     2
21      |          32 |    10
22      |          31 |     6
22      |          32 |     6

Now I want to find the items which correspond best to a search pattern (weighted categories), e.g. category_31=5 and category_32=4. By hand I would calculate the scores like this:

item 1: 5*2 + 4*10 = 10 + 40 = 50
item 2: 5*6 + 4*6  = 30 + 24 = 54

Based on this search pattern, item 2 would get a better score. At the end of the day I want to implement this with Java JPA and a MySQL database (migration to PostgreSQL in planning), but I hope to discuss this issue independent of the DB. Does this pattern has a specific name (to read more about it and better tag this question)? What would be the best (fast) approach to get results with a query? The categories are rather limited (between 15-20), but additional categories may be added. The number of items are > 10.000.

I am not fixed to the above data structure, if someone can suggest a better alternative, I'm open for it.

Your Answer

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

Browse other questions tagged or ask your own question.