Forum Moderators: open

Message Too Old, No Replies

tags and normalization

how to normalise user created tags

         

kunwarbs

11:29 am on Sep 17, 2007 (gmt 0)

10+ Year Member



I have a table containing photos (photo_master) and another table containing tags (tag_master)

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

mattur

11:45 am on Sep 17, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Option 3 is the optimal, standard, normalised solution for Many:Many relations.

The other two options store discrete data (ID's) in a non-discrete way (list of ID's) which introduces all sorts of problems in SQL.