Any suggestions for storing tags?

[ralph l mayo]

I'm making a simple image hosting script like imageshack, but in order to make it web2.0 compliant I have to add tags. I'm no DBA, and I'm not sure of the best way to store data with many-to-many relationships, and I was hoping to solicit some ideas. I thought about just giving the image table a fulltext space delimited tag field, which would be really easy to query, but not at all normalized and probably quite slow under strain.

How can I set up my db to scale more gracefully?

tia ;)

[firepages]

I would treat it like any other search, a keywords table + a lookup table
images
(int[primary]) img_id | (?)img_name{whatever/etc}

keywords
(int[primary])word_id | (varchar[unique])word_word

lookup (combined unique index on img_id + word_id)
(int)img_id | (int)word_id


for inserts, add any new words to keywords , and populate lookup with word_id/img_id's

for searches, query the keywords table for word_id's and then the lookup table to see which images they relate to

A lot more work (and 2 queries to search) but still faster as tables get big.

[ralph l mayo]

Yeah, that looks a lot better, thanks. I didn't even consider a third lookup table, although it makes complete sense. Did I mention I'm no dba? :)

[GJay]

for searches, query the keywords table for word_id's and then the lookup table to see which images they relate to

SELECT i.*, t.name FROM images i JOIN images_tags it ON (i.id=it.imageid) JOIN tags t ON (it.tagid=t.id) WHERE t.name='searchterm';