1

I am trying to design a database where I will keep track of different objects related to a publication house. The obvious one is keeping track of all the articles. I would like to add one or more "Tag"s to them. But I am not sure how to handle them.

As a first thought I am thinking of a mapping table consists of Tags and corresponding Sub-tags. And another mapping table of mapping of article to those tad-combination id.

Would it be the best way to manage them and use for searching? How does this stackoverflow tagging a question work?

Plymouth Rock
  • 472
  • 2
  • 6
  • 20

2 Answers2

1

It's a classic many-to-many situation that you solve with link tables.

If tags and sub-tags are equivalent, then you have two core tables, Articles and Tags, and one link table ArticleTags. If you have a pressing need to differentiate sub-tags, then you have three core tables and two link tables, Articles, Tags, and SubTags linked by ArticleTags and TagSubTags.

The single level of tag is easier to manage and will be more performant, although whether or not it actually matters will depend on a variety of factors. If sub-tags are named very similarly then a like search will still retrieve them, if all tags are in a single table.

To search for a articles by tag you would probably use SQL like this:

SELECT a.* FROM Articles a JOIN ArticleTags at ON a.Id = at.ArticleId JOIN
Tags t ON at.TagId = t.Id WHERE t.TagName Like '%your search criteria%'

If you are constructing SQL like this in any public environment be VERY careful to ensure you bind your parameters correctly to avoid SQL Injection Attacks link1 link2 link3

If you have distinct sub-tags then your SQL will need to be something like this

SELECT a.* FROM Articles a JOIN ArticleTags at ON a.Id = at.ArticleId 
JOIN Tags t ON at.TagId = t.Id JOIN TagSubTags tst ON t.Id= tst.TagId
JOIN SubTags st ON tst.SubTagId = st.Id
WHERE t.TagName Like '%your search criteria%'
OR st.SubTagName Like '%your search criteria%'
Community
  • 1
  • 1
ste-fu
  • 6,879
  • 3
  • 27
  • 46
-1

In case you are using PostgreSQL as database you can make use of the Array type to store multiple values in one column. I covered this in one of my talks, see the slides here: https://talks.bitexpert.de/phpuk15-postgres-nosql/#/12

Stephan Hochdoerfer
  • 104
  • 6