Topic awaiting preservation: setting up a keyword system for database

I have been wanting to add keywords to my links databse and image gallery, but am unsure how best to implement this.

I have done some google searches, but most of the results seem to be in regard to how great 'tag clouds' are, or offer scripts to download (some of which had 50 + php documents to install!! ), or are opinion pieces on the merits of technorati tags. I really don't care about any of those things.

What I want to do is simple in concept: each entry into the database can have any number of keywords. These can be pre-existing keywords or not.

The question: how store them, and how to relate them. Is a standard relational setup, with a table for keywords, and a table for the relationship for the keyword > database entry the best way to do it, or is there a better way?

Any tips/thoughts greatly appreciated.

I haven't investigated in that but I doubt it can be easier to implement and search than with a keyword table plus relationship table. The speed must also be quite decent this way.

quick answer: yes it is.

Good enough - I'll get to work then. =)

Just wanted to make sure I wasn't overlooking something...


thanks =)

tag == keyword

You will have your existing tables (images, links) and you will need a has_many_and_belongs_to_many table for each of the tables that you will want to tag.

create table {table plural}_tags (
id int,
{table singular}_id int,
tag_id int )

create table tags (
id int,
name varchar)

Get all tags based on your link id:

select tags.name from tags 
join {table}_tags link on link.tag_id = tags.id 
join {table} on {table}.id = link.{table}_id where {table}.id = ?

Get all links based on a tag:

select * from {table} 
join {table}_tags link on link.{table}_id = {table}.id 
join tags on tags.id = link.tag_id where lower(tags.name) like lower(?)

That is at least how I implement it.

Dan @ Code Town

Well, it took me five minutes to start understanding Warmage's post.
I should get some sleep I guess.

You may want to take a look at the third method on this page :

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

Basically it does the same, but the syntax is different.

Don't ask me which one is best

----
If wishes were fishes, we'd all cast nets.

I'm not worried about the specific set upor syntax and what have you - that's all well in hand. Just wanted to make sure my general approach was the right one - seems from the responses and from your link,moonshadow, that it was.

Thanks for the link.