Multiple Labels (think Gmail labeling)

[lemist]

I'm writing my own photo gallery script because I have managed to find something I don't like with every gallery I've tried. I need to figure out how to assign a photo to multiple galleries (Say a picture of one of my school's buildings would be in the galleries Buildings and Campus). These are the options I've worked out so far:

1) Have a separate table for the gallery associations. I.E. assign a unique ID to each photo and in the gallery association table have a new row for each gallery the photo is in. (This is probably the easiest solution, but I would rather not have to query multiple tables).

2) Somehow have the "galleries" field contain an array (note: this can't be a regular PHP array because I would have to serialize it, that would severely impact performance if I queried for all photos in a given gallery, as I would have to retrieve every row and then look at each array).

3) Any other ideas?

[Roger_Ramjet]

Standard Many to Many problem: photo can be in many galleries, each gallery has many photos.

Standard solution:

aditional table that forms the matrix of gallery-photo associations

gallery table
gall_id, name, etc

photo table
photo_id, subject, etc

gall_photo table
photo_id, gall_id