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

Question

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?

Answer 1

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! :)

Answer 2

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