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

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.

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?

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