Tag synonyms in DB schema with three columns

Question

Assume you have the same data model as described here.

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

If you want to have tag synonyms similarly as are on stackoverflow, where would you place this information in the data model? Would you add only one attribute with comma-separated list of synonyms or would you transform a data model a little?

How the SQL statement for inserting new item would look like? Will the new item be connected with all the synonyms or only with one main tag and in the code we have to detect that this is a synonym?

Which approach would you recommend for smooth inserting, deleting and searching by tag operations?

Answer 1

Change the Tag table and add a TagSynonym table:

Table: Tag
Columns: TagID

Table: TagSynonym
Columns: TagID, Title

The TagSynonym.TagID would be an FK to Tag(TagID) and the Title would be the PK.

---

If you also want to have a "MainSynonym" tag, then you could use this:

Table: Tag
Columns: TagID

Table: TagSynonym
Columns: TagID, Title

Table: TagMainSynonym
Columns: TagID, Title

with TagMainSynonym(TagID) being the PK and TagMainSynonym(TagID, Title) being an FK to TagSynonym(TagID, Title).

It may look overdue but a simple operation like merging two tags will only need a simple UPDATE (one row) on the Tag.TagID and the cascading effects will do the rest (in 3 tables)