CODEWITHSUNDEEP
postgresql
user3791372
user3791372

Reputation: 4665

Storing and accessing related objects in postgres

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?

Upvotes: 2

Views: 555

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246898

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);

Upvotes: 1

Related Questions