CODEWITHSUNDEEP
sqldatabasetagsdata-modeling
sçuçu
sçuçu

Reputation: 3070

Data modeling for tags with synonym tags on an SQL database

I want to build a tagging system that have a data model defined in SQL. I will tag the posts like the way SE does.

What I need is having tag synonyms.

Which of the following/s can permit future expansion of the set of the tags with synonyms? And if none or there is a way better way, what is it?

I have currently these three ideas for this.

1. First one have only one table, tags, that hold all tags and synonym tags in one table in the db, and has following fields, (to be brief I have only typed few columns neccessary, and has a pseudo code that is not valid SQL):

tags table:

id(Primary Key) tag_id label synonym_id(References tags.tag_id, on this table)

2. Second one, has two tables as following:

tags table:

id(PK) label

tag_synonyms table:

id(PK) label synonym_id(References tags.id)

3. Thirdly, two tables again, one for tags one for tags relations on the single same tags table itself:

tags table:

id(PK) label

tag_to_synonym table:

id(PK) tag_id(References tags.id) synonym_id(References tags.id)

Upvotes: 0

Views: 212

Answers (1)

Rodrigo Cava
Rodrigo Cava

Reputation: 173

Not sure if the second option will work correctly as intended, but the right answer is (as so many things in life) depends :)

If your DB is a normal relational database (e.g. MySQL, Postgres, etc), the third one is more normalized so should scale better and perform relatively well if you make some indexes. But if you have just a few records (e.g. less than 1 million records) I'll use the option one since it's simpler.

But, if you're planning to scale it to several million rows, you should consider using a no-SQL DB like Mongo, since it'll scale better and be faster for this particular case. Unfortunately, this will require a bit change of mindset on how data model.

Upvotes: 1

Related Questions