Attribute comparison algorithms?

I am just wondering if anyone may be able to help. I have a site whereby an item has a set list of attributes.

Each user also has a list of attributes that they “prefer” their items to have.

I am after some sort of algorithm to match a user up with their “perfect” items.

Does anyone have any idea where to start looking or if there are any books I should checkout?

I am using PHP and MySQL.

What does the table schema look like – something like this?

users

  • id (pk)

attributes

  • attribute (pk)

items

  • id (pk)

users_preferred_attributes

  • attribute (attribute foreign key)
  • users_id (user foreign key)
  • pk(attribute,users_id)

items_attributes

  • items_id
  • attribute (attribute foreign key)
  • pk(items_id,attribute)

Query would than look something ~like~ this to find all the items that match a users attributes (untested).


SELECT
      i.id
  FROM
      items i
  INNER
   JOIN
      items_attributes ia
     ON
      i.id = ia.items_id
   LEFT OUTER
   JOIN
      users_preferred_attributes upa
     ON
      ia.attribute = upa.attribute
    AND
      upa.users_id = 90
  GROUP
     BY
      i.id
 HAVING
      COUNT(*) = COUNT(upa.attribute)

The trick is to count all rows in each group and make sure that count is equal to the number of user attributes. Any item that has a attribute that a user does not will dropped from the from result set because the totla count or rows in the group will not equal the total user attributes matched.

Ah superb.

Thank you very much.

I took what you did and modified it slightly to allow returning of results where the count does not match however is still high. I just took:

((count of matched attributes / total item attributes) * 100) AS correlation

then

ORDER BY correlation DESC

Thank you again.