MySQL Database Design – SET vs VARCHAR for Tags in Photos Table

database-designmariadbMySQL

I just discovered the SET column type for MySQL tables. I have a photos table with ARIA engine where I want to store tags/keywords for each photo.

Now I use a VARCHAR type and then find the photos with a MATCH query and a FULLTEXT INDEX, the tags are space separated like "one two three".

When I use SET, I could possibly use tags with spaces, like "a tag with spaces, another one", but how fast would this be? And can I use FIND IN SET to list photos by tag and also list all tags from a range of photos?

Which is the better choice for tags, SET, VARCHAR or even another type?

Best Answer

Neither

Add another table for tags with one row per tag and photo

  • either just (photo_id, tag) - tag is varchar (or maybe enum, but then it is harder to add more tags), so if two photos have the same tag, there is some redundancy
  • or two tables - (tag_id, tag) and (photo_id, tag_id) where each tag is unique, so no duplication of texts, and the second table just links tags to photos, thats more effective but a bit more complex

Do not store multiple values in one column if you want to work with them (search by them etc.) - a database is not just a CSV storage and this is what rows and joins are for. It's called Normalization - learn about it and it will save you from many headaches.

Related Question