Sfrgttk

What happens if you declare more than $10,000 at the US border?

How would a coma-like death system work on an alien world?

Ramanujan's radical and how we define an infinite nested radical

Why is the meaning of kanji 閑 is "leisure"?

find command cannot find my files which do exist

Are encryption algorithms with fixed-point free permutations inherently flawed?

How does the income of your target audience matter for logo design?

How to know if I am a 'Real Developer'

I hate taking lectures, can I still survive in academia?

Where can I educate myself on D&D universe lore, specifically on vampires and supernatural monsters?

Is Screenshot Time-tracking Common?

Is it possible to detect 100% of SQLi with a simple regex?

boss asked me to sign a resignation paper without a date on it along with my new contract

What is the source for this Leonardo Da Vinci quote?

What is the benefit of assigning null to some parameters in construct?

What does “to the numbers” mean in landing clearance?

A semicolon (';') is not needed after a function declaration. C++

Short Story - Man lives in isolation in a compound, plays tennis against a robot, arranges meeting with a woman

Can I legally make a website about boycotting a certain company?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Why do BLDC motor (1 kW) controllers have so many MOSFETs?

The Late Queen Gives in to Remorse - Reverse Hangman

How to play songs that contain one guitar when we have two or more guitarists?

Badly designed reimbursement form. What does that say about the company?

DB Structure and Search with weighted ratings

How can I model and enforce constraints on a categorical item contained in a collection?Schema/Structure to handle secondary computed dataDatabase schema design for individual configurationsCreating Database Tables for Reviews functionality that incorporates Tags/CategoriesHow should I use DynamoDB tableHow to model an entity type that can have different sets of attributes?mongo db designPostgreSQL: optimize Rarity Score calculationWhat is a good schema for a database where users can search for inventory availability on certain dates (such as Airbnb)?Double associative entity in a business environment involving Order Item Reviews?

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

--------+-------------+-------

1       |           1 |     2

1       |           2 |    10

2       |           1 |     6

2       |           2 |     6

Now I want to find the items which correspond best to a search pattern (weighted categories), e.g. category_1=5 and category_2=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.

database-design optimization query

share|improve this question

edited 1 min ago

Thor

asked Feb 17 at 15:32

ThorThor

1212

add a comment | 

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

--------+-------------+-------

1       |           1 |     2

1       |           2 |    10

2       |           1 |     6

2       |           2 |     6

Now I want to find the items which correspond best to a search pattern (weighted categories), e.g. category_1=5 and category_2=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.

database-design optimization query

share|improve this question

edited 1 min ago

Thor

asked Feb 17 at 15:32

ThorThor

1212

add a comment | 

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

--------+-------------+-------

1       |           1 |     2

1       |           2 |    10

2       |           1 |     6

2       |           2 |     6

Now I want to find the items which correspond best to a search pattern (weighted categories), e.g. category_1=5 and category_2=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.

database-design optimization query

share|improve this question

edited 1 min ago

Thor

asked Feb 17 at 15:32

ThorThor

1212

item_id | category_id | rating

--------+-------------+-------

1       |           1 |     2

1       |           2 |    10

2       |           1 |     6

2       |           2 |     6

item 1: 5*2 + 4*10 = 10 + 40 = 50

item 2: 5*6 + 4*6  = 30 + 24 = 54

share|improve this question

edited 1 min ago

Thor

asked Feb 17 at 15:32

ThorThor

1212

share|improve this question

edited 1 min ago

Thor

asked Feb 17 at 15:32

ThorThor

1212

asked Feb 17 at 15:32

ThorThor

1212

asked Feb 17 at 15:32

ThorThor

1212