How to search for multiple synonyms?

Question

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.

score 1 · Answer 1 · answered Dec 27 '20 at 11:35

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;

How to search for multiple synonyms?

1 Answers1