I'm not actually planning on doing this any time soon, but I'm curious what would be the best way of achieving something like this. If you're not familiar, Amazon recommends products to you based on what stuff people bought who also bought the thing that you are looking at. The only way I could think of doing it would be to log every ip and their respective purchases. Then when the script is queried it would scan every row for someone who bought that item, tally up all their other purchases together and find the 5 most popular. Then the page could be cached. I can imagine this would be a very consuming process on large databases though. Can you think of a better way to do it?
I've also considered this for a couple of sites and I think your concept is pretty much the only way to do it. Amazon, of course, only works from registered users and I imagine there'd be limitations imposed on the search criteria to trim the results. I'd probably approach it from the back end, as it were, by building/maintaining a table with the recommended products, rather than running a query to try and find them. You're primarily interested in spontaneous sales so I'd guess the primary driver would be sales volume in some way and I'd probably start by seeding my 'recommendations' table with the top X sellers. I'd then apply some process at the POS to update/amend this table based on what's being bought. I think the key to speed is to limit the number of rows in this 'recommended' table. You wouldn't necessarily need to have every item in your catalogue cross-linked with every other in some huge sales/link lookup and I'm pretty sure Amazon don't (or at least didn't initially). It's an interesting challenge though, I'd love to know what they actually do. Jon