mysql picasa like photo tags store and query [closed]

Question

I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql.

Tags could be words or short sentence, but total length can not over 80 alphabets by each. Users can make the tags what they like.

I wanna to ask, what is the best way to design this kind of database? easy for store and query. BTW: Also need a easy query to see which tags are more popular(show 10 populars). Thanks.

Answer 1

If the number of tags allowed will never be greater than 10, then the simplest solution is to have ten 80-character fields attached to the photo table, named tag1, tag2,...

This is a very simple way to do it, but could lead to problems. If you want to have more or less tags in the future (and I see this sort of thing happen very often), you'll have to change the table structure. You also have to think of what happens when a user adds some tags, then removes one, then adds a different one: how do you decide what value to drop into which tag_n fields?

Another approach would be to normalize the tags and have a separate tags table, and then a tags_photos table to keep track of which tags have been applied to a specific photo.

This is slightly more complicated in terms of structures, but if you choose later to allow more tags, nothing needs to be done and if you decide to reduce the number of allowed tags, old photos could be permitted to keep their larger number of tags. You also remove redundancy by moving the tags to a separate table and referencing them by a tag_id.

Personally, I prefer the second approach. It's cleaner and more flexible.