What do I need in a database for "Customers Who Bought This Item Also Bought"?

Amazon has "Customers Who Bought This Item Also Bought".

I am wondering and want to add this to my shopping cart which I made.

What fields do I need in a database? Any website, blog or resources for this?

Can you suggest the mechanism开发者_开发百科 how to I should code it please?

Here are some similar questions:

• How do recommendation systems work?
• Design of the recommendation engine database?
• How to create my own recommendation engine?
• Techniques for building recommendation engines?
• Where can I learn about recommendation systems?

You probably don't need any new fields in your database - just keep a history of your orders. Then when you want to find your list of what other people bought:

1. Select all users who have an order containing Item X
2. For each of those users, total up everything else they have bought
3. Get the top 3/5/whatever and there's your list.

It's not too tricky. Assume you have the following tables:

• Customers, primary key CustomerID
• Products, primary key ProductID
• Orders, primary key OrderID, foreign key CustomerID
• OrderItems, primary key OrderItemID, foreign keys OrderID, ProductID

To find the products you seek, you need to find the set of customers who have bought that particular product ID:

SELECT CustomerID
FROM (Customers INNER JOIN (Orders INNER JOIN OrderItems))
WHERE OrderItem.ProductID = <your product id here>

Then, you need to get the other products those customers have bought:

SELECT ProductID
FROM (Customers INNER JOIN (Orders INNER JOIN OrderItems))
WHERE (Customer = <given customer ID>) AND (ProductID <> <your product id>)

Then select the top few products and you're off to the races.

Note: I'm a numerical guy. The DB gurus will be able to do this in 1 query! :)

You need history of orders so that you can check for other items that were bought together with the item user is currently viewing.

You need "Programming Collective Intelligence". They have some nice chapters about recommendations and such. You'll want to read about Pearson differences and other measures.

Have a look at Algorithms of the intelligent web, chapter 3 "Creating suggestions and recommendations". For your question: optionally, you might need a table with user ratings for different items. Based on these ratings, you will be able to measure similarity between two clients and then perform an estimation based on these values on the items one of client is about to achieve. These estimations are used to rank the items.

Also, have a look at the Apriori algorithm chapter 4 or a general description of it here; this works for items bought together and extract some associations rules. Based on these rules, you will detect which of the items you sell might be added to the client's basket. For your question: no additional field should be added to your database; you have to maintain only a table to group items purchased together (market baskets contents).

select `A`.`ORDER_NO`, `A`.`SKU`, `B`.`SKU` `REL_SKU`
from `order_detail` `A`
inner join 
    (select DISTINCT `ORDER_NO`, `SKU` 
        from `order_detail`) `B`
    on `B`.`ORDER_NO` = `A`.`ORDER_NO`  
WHERE `B`.`SKU` = 'XXXXXXXX'
  AND `A`.`SKU` <> 'XXXXXXXX'

This works for me. of course add in any filters in the where clause as appropriate such as order status etc...