Have Geek Brain

Database tagging structure

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

I've done this before, having a tag table to define my tags and a lookup table that has type (news, tutorial, overview), itemId and tagId.

This works great, however for my new site I want to have PK FK relationships between tables (since I am using linq for entities).

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

What's the best way to do this?

+2


a source to share


1 answer


you can 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
+2


a source






More articles:


All Articles