How to search for multiple synonyms

datasynonyms

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.

Best Answer

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;
Related Question