"Floris van den Berg" <flvdbergwxs.nl> wrote in message
news:bgm011$7hq$1reader08.wxs.nl...recommendations,>> > On Mon, 04 Aug 2003 15:57:31 +0200, Floris van den Berg wrote:> > > - per user keep a history of visited products
> > > - per product keep a list of related products
> > > - show the related products of the visited products asanother.> but>> > > only if these are not already purchased or flagged as not interesting.
> > >
> > > Any thoughts if i could do it better?
> I indeed keep track of what customers ordered in a table, as well as what
> products they watch. But knowing what someone ordered or watches, doesn't
> tell me what other products these customers might be interested in.
> Currently i keep a 'relation' table, where i relate one product tothat> I show the contents of this table everytime someone views a product, sothem> they can easily find other items they might be interested in (and keepand> on the website).
> The relation table is rather static, because i set it manually from my
> content files (i wrote a tool that takes a number of modified ini-filessure> outputs pictures and sql). I would like to make things a bit more dynamic.
> Somehow gather statistical data that allows me to dynamically relate one
> product to another based on what products they watch and purchase. NotYou're on the right track by creating a "related products" table for items> how to go about this though.
that are similar. The easy way to solve your problem is to grab a list of a
single visitor's purchase, and create links from each of these products to
the other in your RDB. This will create a Many-to-Many relationship within
a single table. You won't want to modify the products themselves, but
rather an external linking table. The linking table would look like this:
create table product_links_internal (
id_source int not null,
id_target int not null,
strength int not null default 1 );
I added a non-RDB column called "strength" to indicate the statistical
strength of the link. This is, at best, a kludge, but works rather nicely
for being as simple as it is.
For now, pretend this is your products table:
create table products (
id int not null auto_increment primary key,
name varchar(127) );
Say someone orders three games:
Game 1: id = 1, name = "Pong"
Game 2: id = 2, name = "Centipede"
Game 3: id = 3, name = "Wolfenstein 3D"
If you want these products to now have a set of relationships with each
other, just check to see if they are already linked, and, if not, create a
new link. Note: since this is an internally-linked list, you should always
link ascending items to prevent "cross-links" which would undermine your
For these items, I would sort the order by "id," then check for links.
Let's assume only Pong and Centipede are linked together. And, Wolfenstein
3D is not linked to any of them.
Therefore, we will have a record already established for the 1->2 link with
some unknown strength value. In this case, we would want to increase the
strength of this relationship by updating the record:
update product_links_internal set strength = strength + 1 where id_source
= 1 and id_target = 2
Since Wolfenstein is not related to the other two games, it will need to get
its first link set to both games:
insert into product_links_internal (id_source, id_target) values (1,3)
Now that you have a list of links with a relative rating for "relatedeness,"
you can start to display a list of "related items" without anything too
select products.* from products,product_links_internal where product.id =
product_links_internal.id_source or product.id =
product_links_internal.id_target having product_links_internal.id_source =
$CURRENT_PRODUCT or product_links_internal.id_target = $CURRENT_PRODUCT
order by product_links_internal.strength desc limit 5
The value for "$CURRENT_PRODUCT" is supplied by your reporting script and is
the id of the item for which you currently wish to display a list of
"related items." This query will return the top 5 related items for any
given item. (Remove the "having" clause to see a list of the top 5
I've used a similar method for a community-managed content system (a fancy
term for a bulletin board that is self-managing). I came up with the idea
when I was drawing out my RDB diagrams and wanted a way to represent a
really thick line connecting two records and relatively thin line connecting
two records. The "real" RDB method would be to create a new instance of the
link each time and add up the total number of links when reporting, but
since we're in a web-based environment, I decided to break that rule in
favor of depleting processor resources on the backend instead of the
Hopefully, this will give you some ideas as to how to solve your particular