Best Way to Implement Tags (Similar to StackOverflow) [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

How do you recommend implementing tags or tagging

I have a website with a database that contains a number of articles开发者_开发技巧. I'd like to implement tags similar to the tags on stackoverflow.

I can think of two basic ways to implement them:

1. Create a separate Tags table with a one-to-many relationship with my Articles table.

2. Add a Tags text field to my Articles table.

The first approach seems the best but would require two additional tables that would grow quite large. It seems like there would also be considerable overhead updating and maintaining that data.

The second approach would be far easier to implement and maintain, and use less resources. But searching would be less efficient. I'd probably use LIKE or maybe even full-text searching.

I'm interested in which approach others think is best. Or perhaps there another approach altogether.

---

I would personally go with option 1, You mention two additional tables later so I assume you're thinking of.

Table -Tag
Fields - TagID, TagName

Table -TagArticle
Fields - ArticleID, TagID

Table - Article
Fields - ArticleID, blah, blah, blah

This shouldn't require much more in the way of storage than dumping to a field in Article. Plus it is normalised which will always stand you in good stead for the future and will leave your database far better able to search for articles by tag. As for updating, chances are you'll only be updating occasionally compared to the number of times you're reading so the impact should be negligible and I cant think of any maintenance tasks beyond ensuring your indices are up to date which you're going to have to do on other tables anyway and should be automated.

Fringe benefits mean you can quickly create things like a top tags list or a tag cloud.

---

The first option is clearly the best of the two. This works with the relational model, and leaves your data normalized. The second option works against the relational model, and breaks normalization. How are you going to run queries such as "give me the top 10 most popular tags"? Or "how many times has the tag 'x' been used?" These queries become trivial with option 1, especially as (assuming Robb's schema) you can keep a Count column against each tag.

Option 2 gains you a slight simplification for a great loss in functionality (and in the long run, I contend, efficiency too). The relational model is tried, tested and works! Use it!