Storing and accessing related objects in postgres

Question

I have a products table, and a separate table that I'd like to store related products in which consists of two fields: create table related_products(id1 int, id2 int) and indexes placed on each field. This would mean that I'd have to search both id1 and id2 for a product id, then pull out the other id field which seems quite messy. (Of course, one product could have many related products).

Is there a better table structure for storing the related products that I could use in postgresql?

you mean that the product has many2many relation between its records — Charif DZ
– Charif DZ, Commented Apr 5, 2017 at 14:57

Laurenz Albe · Accepted Answer · 2017-04-05 15:09:59Z

1

That is not messy from a database perspective, but exactly the way it should be, as long as only pairs of products can be related.

If you want to make sure that a relationship can be entered only once, you could use a unique index:

CREATE UNIQUE INDEX ON related_products(LEAST(id1, id2), GREATEST(id1, id2));

To search products related to product 42, you could query like this:

SELECT products.*
FROM products
JOIN (SELECT id2 AS id
         FROM related_products
         WHERE id1 = 42
      UNION ALL
      SELECT id1
         FROM related_products
         WHERE id2 = 42
     ) rel
   USING (id);

answered Apr 5, 2017 at 15:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3791372 Over a year ago

is there a performance difference between using an explicit join and a

select * from products p where id in((SELECT id1 FROM related_products WHERE id2 = 1 union all select id2 from related_products where id1 = 1));

Laurenz Albe Over a year ago

Probably not - I usually prefer joins, but that's a matter of taste. Compare the plans and execution time to make your choice. Try with a) few result rows and b) many result rows in the UNION.

Collectives™ on Stack Overflow

Storing and accessing related objects in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related