Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

closed as not constructive by jcolebrand Apr 27 '12 at 4:43

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 2 down vote accepted

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.