How would I implement tags within a database?

Question

I have an app and I want users to be able to add tags to articles (similar to stack overflows tags) but I want it to be dynamic. So far I have it as JSON strings (dont squirm too much) but this has a major shortcoming. Firstly I will show you an example, and then explain my problem.

Say I have an article and it's on bees, so users tag: bees, insect, honey, outdoors.

Then in my article class (entity framework) I have

string AssosciatedTags { get; set; }

which would hold: "[\"bees\", \"insect\",\"honey\"]"

then when I render the article I just do in javascript:

JSON.parse(model.AssosciatedTags); 

and do whatever I want ... add/remove tags with ease. Ok, so here's where I didn't really think enough, and now I have I still am rather confused. How can I implement a sorting mechanism? Let's say my users are bee crazy. They love bees. Their cute little black and yellow stripes. So they want to click tags they are interested in and my server returns articles that have those tags assosciated.

But how? If I have lots of articles, it seems a bad idea to parse all tags from JSON to an array, look for this tag and return those articles.

My other consideration is to simple to a lookup for ",tagName," which is pretty filthy.

Is there a standard way or one that is hopefully more optimal.

Answer 1

My other consideration is to simple to a lookup for ",tagName," which is pretty filthy.

Yes, that is filthy. This technique has it's place but not here.

Store the tags in another table with the schema int ArticleID, string TagName. That way you can index TagName and query it efficiently.

This is the standard solution.

If you want to you can keep the JSON string but you need to keep it in sync with the table storing the tags. Preferably remove it, or be very careful.