Dynamic compatibility matrix

[rmarescu]

Hello,

I'm working on a project and I need some kind of dynamic compatibility matrix between items.

Let me explain using an abstract example

I have a table with all the items for that I want to set some compatibilities:

Table: Items (ItemID, ItemName):

1  | apple
2  | pear
3  | fistic
4  | ketch-up
5  | meat
6  | salad
7  | tomato
8  | ice-cream
9  | water
10 | cucumber
11 | peper
12 | salt
13 | soup
14 | roast
15 | sugar

Between these items I want to set up some dynamic compatibilities.
Let's say that I set the following compatibilities:

apple is compatible with: pear, fistic, salad, ice-cream, sugar.
sugar is compatible with: pear, salad
roast is compatible with: meat, peper, salt, sugar
soup is compatible with: tomato, peper, salt, meat, sugar.

Once I have this set up and trying to find out the compatibilities of meat I should find: roast, soup.
Also, the compatibilties of sugar should be: apple, pear, salad, roast, soup.

If I change the compatibilities of peper and remove soup, then the soup's compatibilities should be automatically updated to: tomato, peper, salt, meat.

My main issue is how to design the database for it.
My initial solution was to build a compatibilities table, like this:

Table: CompTable (ItemID, CompItemID):

1  | 2
1  | 3 
1  | 6
1  | 8
1  | 15
15 | 2
15 | 6
14 | 5
14 | 11
14 | 12
14 | 15
13 | 7
13 | 11
13 | 12
13 | 5
13 | 15

But this solution is not dynamic. If I'm searching for sugar, I have to check for ItemID and also for CompItemID and then combine the results. - I don't want that...

Any help would be highly appreciated.

Thanks!

[rulian]

Nope, you are on the right track there.
What you are describing is a many to many relationship model
Meaning each entry in one table could have multiple entries in another.
And the solution is to do exactly what you said, for each relationship you ad a row to the pairing table, and when you match your results you pull id's on both fields.
You could do either via a WHERe = OR = , or using a compound query on table items and join on matching id's

somethign like

SELECT T4.ItemId, T4.ItemName from Items AS T1
LEFT JOIN CompTable AS T2 on T2.ItemId = T1.ItemId
LEFT JOIN Comptable AS T3 on T3.CompId = T1.ItemId
LEFT JOIN Items AS T4 on T4.ItemID = T2.CompID OR T4.ItemID = T3.ItemID
WHERE T1.ItemName = "apple"

I obviously have not tested this query, but should give you an idea