Optmal tags design? - Mailing list pgsql-performance

From lists@on-track.ca
Subject Optmal tags design?
Date
Msg-id 1271.69.31.174.216.1184793960.squirrel@webmail.ctgameinfo.com
Whole thread Raw
Responses Re: Optmal tags design?
Re: Optmal tags design?
List pgsql-performance
I am planning to add a tags (as in the "web 2.0" thing) feature to my web
based application. I would like some feedback from the experts here on
what the best database design for that would be.

The possibilities I have come up with are:
* A tags table containing the tag and id number of what it links to.
select pid from tags where tag='bla'
select tag from tags where pid=xxx.

* a tags table where each tag exists only once, and a table with the tag
ID and picture ID to link them together.

select pid from tags inner join picture_tags using(tag_id) where tag='bla'
select tag from tags inner join picture_tags using(tag_id) where pid='xxx'

* A full text index in the picture table containing the tags

select pid from pictures where tags @@ to_tsquery('bla')
(or the non-fti version)
select pid from pictures where tags ~* '.*bla.*'

select tags from pictures where pid=xxx;

pgsql-performance by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: insert vs select into performance
Next
From: Mark Lewis
Date:
Subject: Re: Optmal tags design?