I'd like to add tags to my app. I have several different resources I would want to apply tags to, such as blogs, picture galleries and a few others. More efficient to add tag columns to each resource table or create a seperate table for tags that refrences each resource.

I can't decide on the trade off.

its a web app written in php5 and mysql5

I'd say I'm an advanced beginner at this stuff so my current implementation shows it.

Thanks

It would be better to store the tags in a seperate table as you said with a reference to them in a cross reference table. This also has the benefit of avoiding duplication.

Many to Many.

Tables:
Posts (id, title, text, etc)
Tags (id, name, description, etc)
PostsTags (postsid, tagsid)

I am in process of doing something similar for one of my web sites.

I would add another question here for anyone to discuss. Is there a better design that allows to group tags so that they don't grow exponentially over time.

Imagine tags like: tag, tags, the tags, tagg, etc.

Any ideas?

Tim

it would be better to store the tags in a separate table and maintain relationship by tagid or somthing that is more suitable according to the condition.

Thanks for reply.

You are right. That is exactly the approach that I am going with. Basically 2 tables where one holds the tag_id's and is easily searchable.

However, the solution looks too simple and possibly not very efficient. Is there any other solution that could be more efficient or is this as far as it goes?

I am talking about efficiency since I expect that there will be 300,000+ records (at least these are clients' estimations)

why are u thinkin g that 3 M records is too much. Creating index on useful column will solve all your problem.