How to implement tagging if sharing tags with different entities?

I know the tagging stuff has been mentioned many times, but I can't seem to see any that address this question.

From this thread Recommended SQL database design for tags or tagging

I can see the best way to setup tags is likely

Item (item_id, item_content)

Tag (tag_id, tag_title)

TagMapping(map_id, tag_id, item_id)

But what if I want to share the tags between two different item entities? For example, blog posts and articles. (let's not discuss if that should be separate entities or not :)) Do you do开发者_StackOverflow something like this which seems very wrong?

 Post (post_id, post_content)
 Article (article_id, article_content)

 Tag (tag_id, tag_title)

 TagMapping(map_id, tag_id, post_id(nullable), article_id(nullable))

---

The orthodox answer would be to use a table inheritance strategy between Post and Article. Make them both kinds of Item. So (this SQL could be complete nonsense, but you get the idea):

create table Item (integer item_id primary key)
create table Post (integer item_id primary key references Item, varchar(2000) post_content)
create table Article (integer item_id primary key references Item, varchar(2000) article_content)
create table Tag (integer tag_id primary key)
create table TagMapping (integer item_id references Item, integer tag_id references Tag, constraint primary key (item_id, tag_id))

So both articles and posts exist in the item table, and that's what tags refer to.

You retrieve posts and articles as you do now. If you want to retrieve them by tag, you do a join via Item. If you want to retrieve the details in that join, you have to do an outer join to both Post and Article, and then ignore the null columns.

You very often also have a column in the parent table, here Item, that is like 'integer subtype_discriminator not null', which then contains, say, 1 if the item is a post, 2 if it is an article, etc. That makes it a bit easier to know which columns to ignore, or where to look for details if you want to do a second query for them after a simple join.

---

You can attach tags to a "Tag Board" entity and attach one to all entities that should be tagged:

TagBoard (tagboard_id)

Post (post_id, tagboard_id, post_content)
Article (article_id, tagboard_id, article_content)

Tag (tag_id, tag_title)
TagMapping (map_id, tag_id, tagboard_id)

Conversely, you can use inheritance to make both Post and Article inherit from a "Taggable" entity (or, to allow more flexibility, from a generic "Item" entity). Drupal, notably, does this (with posts and articles and almost everything else being nodes in the Drupal database).

---

If you want to maintain a relational mapping for this schema (as opposed to using a document-based solution like MongoDB where your tags would be denormalized) then you can create one more level of mapping.

The convention I use in this situation is to have every item of the same kind be referred to as an 'ilktype' and each individual item be referred to as a 'rubric', so the ilktype blog post has many rubrics (posts).

How this would look in a schema is the following:

Post(post_id,post_content)
Article(.....)
Foo(.....)
Tag(tag_id,tag_title)
TagMapping(map_id,tag_id,ilktype_id,rubric_id)

You then maintain a list of ilktypes, either as constants in a config file or in an ilktype table, and then can retrieve the item referred to in the tagmapping table by joining the ilktype_id to the appropriate table (post/article/foo).