Database Tagging Structure

Question

Hi,

I have three tables in my database. News, reviews and tutorials. I would like to implement tagging functionality.

I have done this before by having a tag table to define my tags, and a lookup table which has the type (news, tutorial, review), itemId and the tagId.

This has worked fine, however for my new site, I want to have PK FK relationships between the tables (as I am using linq to entities).

How can I do this? The tag lookup table cant be the foreign key for news, reviews and tutorials because when I add an row to the lookup table, a value will need to exist for all three types!

Whats the best way to go about this?

Answer 1

you could try this:

News
NewsID      int auto increment/identity pk
....

Reviews
ReviewID    int auto increment/identity pk
....

Tutotials
TutorialID  int auto increment/identity pk
....

Tags
TagID       int auto increment/identity pk
.....

TagUsage
TagUsageID  int auto increment/identity pk
TagID           fk to Tags.TagID
NewsID          allows nulls fk to News.NewsID
ReviewID        allows nulls fk to Reviews.ReviewID
TutorialID      allows nulls fk to Tutotials.TutorialID