Best way to relate products

Hi Chaps

I'm trying to set up a database to be used in an online shop. I want to record a set of related items to a product so that when the customer finds a product they want to buy, we can suggest what else they might need with it or what might complement their purchase.

Does anyone know the best [simplest] way of modelling this so that you can both add a productid to a list of productids that are related and query the database and get out all the related items for a productid. It's difficult to add an intermediate table to one table instead of two.

The way I was starting to do it got big very quickly - I did a table with [id,] productid and relatedproductid and for records 1,2 and 3
I had 1:2, 1:3, 2:1, 2:3, 3:1, 3:2 as 6 different records

Any better ways out there?

Thanks in advance for your help
StevieBoy

Not sure this'll be much help as I'm not speaking from experience but not sure there's another way to do it really.

I guess if the direction in which they were related is not important you could just use 1:2, 1:3, 2:3 and read the connection both ways but it's probably not a good idea.

Don't really see why you need the [id] field, I assume productid and relatedproductid and the same and unique.

Perhaps some normalization sites can help ...

Hi,

You might try storing a string with each record that contains a comma delimited list of other item ID's. Then, when your user wants to see other related items, your code will take this text field and use it within an "in" clause such as select * from items where ID in ( other_items) order by ...

where other items is a pointer to the current records list of associsted times

Excuse my syntax as I wish to give you the idea.


You will have to do some string programming in order to allow editing this list of items, and you will have to handle the none and the one case and the comma, as well as making sure that the current item can not go into its own list, but this is a fairly easy, non-relational solution to the problem.

Also, I've had difficulty with long "in" lists so watch out for that.


Ray

this is a classic many-to-many relationship

besides the products table, you need another one

create table relatedproducts
( id1 integer not null
, id2 integer not null
, primary key (id1, id2 )
, foreign key (id1) references products (id)
, foreign key (id2) references products (id)
)

assume there is no "direction" in the relationship, i.e. if product A is related to B then B is also related to A

then to store a relationship, just add a row to that table using both ids

always put the lower of the two in id1

to find products that are related to product 123, the query is


rudy