So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag.
E.g for tag: photographer
I'd want to add 'photography, photographers, photo' as synonyms for 'photographer'.
The way I've proposed is that all synoynms are created as tags & that I associate a tag with a synonym by doing a 1<->1 relationship on a second table.
Here is what I'm proposing:
Tag:
- tag_id
- title
- More meta data fields here...
Synonyms:
In this table we can attach a tag as a synonym to another tag (e.g photo can be a synonym for photography)
- tag_id (the tag which we're attaching a synonym to)
- synonym_tag_id (the tag_id which will be a synonym)
So I can add many synonyms to one tag.
Is this solution viable? Could it be improved somehow? I will be writing this schema in postgres. Appreciate any tips :)
Use cases:
- Users of our app will be able to attach tags to there articles. If the user adds photopgraphy as the article title, our search will also show articles that have the photopgraphy synonyms attached as a tag.
- Note that we aren't using SQL for search, we're using elaticsearch. The sql will just be used for storing the data so it can later be imported into elasticsearch.