Forum Moderators: open
photo_master
+---------+-------------+-
¦ photo_id ¦ photo_url ¦
+---------+-------------+-
tag_master
+---------+----------+-
¦ tag_id ¦ tag_name ¦
+---------+----------+-
My users can add a tag to any photo and a single photo can have multiple tags.
Which is the best option to normalize and gives fastest performance?
OPTION 1: create another column "tag_id" in photo_master and insert the tag_id there. Append all the tags to this column when users add a tag to this photo
OPTION 2: create another column "photo_id" in tag_master and insert the photo_id there. Append all the photo_ids to this column when a user adds this tag to any photo
OPTION 3 (i prefer this one): create another table tag_photo
tag_photo
+---------+----------+-
¦ tag_id ¦ photo_id ¦
+---------+----------+-
each time a user adds a tag to a photo, a new row is inserted with tag_id and photo_id