From: | lists(at)on-track(dot)ca |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Optmal tags design? |
Date: | 2007-07-18 21:26:00 |
Message-ID: | 1271.69.31.174.216.1184793960.squirrel@webmail.ctgameinfo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Lewis | 2007-07-18 21:51:52 | Re: Optmal tags design? |
Previous Message | Heikki Linnakangas | 2007-07-18 19:58:02 | Re: insert vs select into performance |