tracking & suggesting objects

[fandelem]

I'm going to be using this in a much broader term, but I will bring in amazon.com just because I'm sure everyone will then know what I'm talking about..

With amazon.com, they have a system set up to "suggest" other material you may enjoy. I'm curious as to what the best way for this is.

Here's what I've come up with so far (I'm just going to cut and paste from my design specs)

/**********************************************************************************
*	INDIE_TRACKING TABLE:
xix.	This table will be used to suggest “similar items” people have browsed.
xx.	This table will be used to track listing relationships. The listing_id is 
	required and must be unique.
xxi.	Here is an example:  Joe-user goes to his favorite bands link.  This 
	listing_id is stored in a session variable.  When joe-user goes to his 
	second favorite band link, the listing_id from joe-users favorite band 
	link will be stored (if already exists, just UPDATE, otherwise INSERT) 
	in a related_object_id (also incrementing the related_object_amount) 
	in the %indie_tracking_2% table.  Will be able to sort by amount where 
	listing_id matches up and pick the highest X objects. With this concept, 
	after a month or so of this tracking, we will be able to display to
	joe-users friend when he goes to joe-users favorite band link the 
	related_object_id listing numbers for him to check out too.
      *
**********************************************************************************/
   CREATE TABLE indie_tracking (
      id INT(16) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	listing_id INT(16),
        ip VARCHAR(16),
        creation datetime,
        last_modified TIMESTAMP(14),
	KEY index_listing_id (listing_id),
	INDEX index_ip (ip(16))
   ) TYPE=MyISAM;

/**********************************************************************************
*	INDIE_TRACKING_2 TABLE:
xxii.	This table is a child table of %indie_tracking% via the %listing_id% field.
xxiii.	Field %listing_id% can be the same in multiple records.  This is used to
	cross-reference the parent table which should only have one listing_id.
      *
**********************************************************************************/

   CREATE TABLE indie_tracking_2 (
      id INT(16) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	listing_id INT(16),
	related_object_id INT(16),
	related_object_amount INT(16),
	KEY index_listing_id (listing_id),
	KEY index_object_id (related_object_id),
       	KEY index_object_amount (related_object_amount)
   ) TYPE=MyISAM;

Any other thoughts on the matter (criticisms, ideas, methods, etc) would be wonderful!

cheers,

k.