Topic: setting up a keyword system for database (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=28897" title="Pages that link to Topic: setting up a keyword system for database (Page 1 of 1)" rel="nofollow" >Topic: setting up a keyword system for database <span class="small">(Page 1 of 1)</span>\

 
DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-08-2007 18:38

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.

poi
Paranoid (IV) Inmate

From: Norway
Insane since: Jun 2002

posted posted 02-08-2007 19:23

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.

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 02-08-2007 19:42

quick answer: yes it is.

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-08-2007 20:37

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

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


thanks =)

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 02-08-2007 21:39

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.

code:
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:

code:
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:

code:
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

Moon Shadow
Paranoid (IV) Inmate

From: Paris, France
Insane since: Jan 2003

posted posted 02-10-2007 02:26

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.

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 02-10-2007 16:38

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.



Post Reply
 
Your User Name:
Your Password:
Login Options:
 
Your Text:
Loading...
Options:


« BackwardsOnwards »

Show Forum Drop Down Menu