#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    NW England
    Posts
    1
    Rep Power
    0

    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
  2. #2
  3. Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    2
    Rep Power
    0

    just a guess ..


    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 ...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    NJ, USA
    Posts
    11
    Rep Power
    0
    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    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
    Code:
    select id2 as productid
         , productname
      from relatedproducts
    inner
      join products
        on id2 = product.id
     where id1 = 123
    union all
    select id1 as productid
         , productname
      from relatedproducts
    inner
      join products
        on id1 = product.id
     where id2 = 123
    order by 1
    rudy

IMN logo majestic logo threadwatch logo seochat tools logo