0

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?

Community
  • 1
  • 1
xralf
  • 3,312
  • 45
  • 129
  • 200

1 Answers1

2

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)

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • I was considering using only one the table Tag with "synonym" column which would contain the name of the main synonym or "main" if it's the main synonym itself. Is it better to have two tables? – xralf Feb 18 '12 at 12:48
  • The `ItemTag.TagID` has to be a Foreign Key to a `SomeTable(TagID)` where `(TagID)` is the Primary Key. But with `TagID` you want (now) to be identifying all synonyms, correct? So, I don't see a way without adding another table. – ypercubeᵀᴹ Feb 19 '12 at 01:30
  • Now, I see. With two tables the implementation of inserting, deleting and searching by tag will be more pure. – xralf Feb 19 '12 at 08:39