1

I have a database with 2 tables : products, and synonyms. When a user type one or several words to search in the products.description field, I would like to search for all the synonyms/substitutions as well.

The synonyms table is basic, and have to columns : word1 and word2. Example of its content :

  • chiptune <-> 8bit
  • lofi <-> lo-fi
  • lo-fi <-> 8bit

In SQL, How can I present to the user results feturing the word "lofi" when he type "chiptune"? What I am trying to achieve is a "loop between synonyms", in both ways.

Thank you if you have some thoughts.

1
  • What DBMS are you using? You can use a recursive CTE to travers the chain. You may instead concider storing groups of synonyms.
    – Lennart
    Dec 27, 2020 at 10:26

1 Answer 1

1

To avoid problems with cycles in your graph, I would suggest that you re-design your structure to:

CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_groups
( synonym_group_id ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE synonym_group_products
( synonym_group_id ... NOT NULL
, product_id NOT NULL PRIMARY KEY  -- A product belongs to exactly one group 
,     CONSTRAINT ... 
      FOREIGN KEY (synonym_group_id)
          REFERENCES synonym_groups (synonym_group_id)
,     CONSTRAINT ... 
      FOREIGN KEY (product_id)
          REFERENCES products (product_id)
);

I created a Fiddle, I just used integer for keys since I don't know what identifies your entities. To get the synonyms of chiptune:

select p2.*
FROM products p1
JOIN synonym_group_products sgp1
    ON p1.product_id = sgp1.product_id
JOIN synonym_group_products sgp2
    ON sgp1.synonym_group_id = sgp2.synonym_group_id
JOIN products p2
    ON sgp2.product_id = p2.product_id
WHERE p1.product_name = 'chiptune'
  AND p1.product_id <> p2.product_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.