Best way to handle TAGS in blog posts

[I <3 Lamp]

Well I guess it doesn't have to be exclusively blog posts, but potentially articles, snippets, images, etc.

For the sake of argument lets say blog posts. Now I have 2 potential ideas but I'm not sure if there is a faster/leaner way to do this so please give me any thoughts you may have on the matter. Also note I am using composite keys just for this example, so do not debate whether they are needed.

METHOD ONE

3 tables:

tblBlogPosts
blog_post_id [primary key]
blog_post_title
blog_post_text
blog_post_author
blog_post_time

tblTags
tag_id [primary key]
tag_name

tblBlogTags
blog_post_id [primary key & foreign key]
tag_id [primary key & foreign key]


METHOD TWO

2 tables:

tblBlogPosts
blog_post_id [primary key]
blog_post_title
blog_post_text
blog_post_author
blog_post_time

tblBlogTags
blog_post_id [primary key & foreign key]
tag_name [primary key]


Obviously the second is a bit more light weight and would require one less join. However if you were to edit a blog post and remove a tag then it would be gone forever, meaning you couldn't track search results or make a tag cloud, etc for removed tags.

Any light you guys can shed on the topic would be greatly appreciated!

[Fumigator]

Method Two isn't normalized because you'd have to duplicate tag names if they were used more than once. Not a big deal if you only have the one column, but as soon as you add more attributes to a tag, now you're having to duplicate all of that tag's attributes. So yeah Method One wins based on the rules of normalization alone.

[I <3 Lamp]

What do you mean more attributes? And doing something purely because it is normalized is weak logic...

[Fumigator]

Speaking more broadly than your immediate requirements list. Maybe later you want to add more info about each tag? Keep your crap normalized and enhancements down the road will be feasible.

I work with 20+ year-old systems written by people who didn't understand normalization (and other "weak logic") and the maintenance on these systems is a nightmare. If you follow wisdom when designing a project at the beginning, even if it may appear to be more hastle for you, you will be glad you did down the road.

And again, I'm speaking more broadly than your immediate task. Maybe your tags won't ever need enhancement; but chances are some system you build will and you should learn good habits and practices for every piece of code you write.

[I <3 Lamp]

You either don't understand what a tag is or are being stubborn. Tags are just a word (or two or three) that can be used to help search for similar blog posts.

I'm aware that normalization tends to make things run a lot smoother, however it's a well known fact that truly normalized databases often have trouble keeping up with popular websites.

[guelphdad]

normalize your data to third normalized form.
properly index your tables.
optimise your queries
tweak your server variables so that your caching is working at its best.
look into the use of AJAX so a query doesn't refresh your browser.

you might be surprised to find that there are a number of sites of significant size that use databases without trouble and normalization isn't the issue.

you may also be surprised to find that the majority of people posting basic questions such as yours do not know anything about normalization. it seems that you do and are worried about overnormalizing your data which is understandable.

[Fumigator]

Yeah that's it, I don't understand what a tag is. :rolleyes:

[I <3 Lamp]

tweak your server variables so that your caching is working at its best.
l

This is probably what I need help with. I don't think I've ever done this before.

Yeah that's it, I don't understand what a tag is. :rolleyes:

I think your last post shows that...

[guelphdad]

When you set up mysql there are four different config files. If you think you are going to be handling significant amounts of data or larger amounts of connections then swap out the config file for one of the others. You'll find them in the root directory of where mysql is installed. they are labelled my-large, my-heavy etc.

As for the tags I think that part of the conversation is redundant. Fumigator is obviously looking ahead as to his ideas of normalization while you are concerned with what you have now. Peace!