Should “tags” be an nvarchar field in a table, or should it be another table altogether?

Question

I am building a photography website and I want to allow the user to put tags on their photos for easy searching. But I'm not sure if I want the tags to be an nvarchar field in each picture and then search for that tag in each record or if I want to pull them out and create a separate table of tags and tagIds and then have a look up table, so I can just search the database for that ID. So, what I'm asking is which would be better efficiency-wise and scalability-wise. The database will have over 3-4000 pictures.

Answer 1

Warning: the question being tagged sql-server, my answer applies to Microsoft SQL Server only, and is not relevant for other SQL engines.

---

There are three approaches for storing tags:

• One table: all the tags of a single photo would be stored in a single nvarchar field,
• Two tables: one for pictures, another one for tags, every tag referencing a picture,
• Three tables: one for pictures, another one for tags, and a third one for links between two other tables.

1. nvarchar field approach vs. other approaches

Don't. Really, don't use nvarchar to store comma-separated (or something-else-separated) tags of a single picture. Databases are done to structure your data, to store data in a logical way. By establishing a convention that several pieces of changeable information will be stored in a single field, you break this logic¹. To be a bit extreme, you can also store all your application data in a flat nvarchar(max), but the fact that you can doesn't mean that you must do it, since instead of using an existent database, you're creating your own, without bringing anything that the existent database does not provide.

Reason 1: complicated queries

If you consider adding features to your application later, storing all tags of a single photo in a single nvarchar is not extensible, and will hurt soon or later. Consider two scenarios:

1. You want to display on your home page the list of tags, ordered by the number of occurrences (so for example if 457 images are tagged sky and 432 images are tagged river, sky will appear before river). Can you do that easily? Can you do that with just one simple query?
2. You want simply to search for pictures containing a tag `river`. Let's say tags are separated by spaces.

select [PictureId] from [Picture] where [Tags] like '%river%'

will give you the pictures with rivers, but also all the photos of Riverside, CA, United States. How sad!

select [PictureId] from [Picture] where [Tags] like '% river %'

will give you some pictures with rivers, but some will be missing.

select [PictureId]
from [Picture]
where [Tags] like '% river %'
    or [Tags] like 'river %'
    or [Tags] like '% river'

gives you the expected set, but now the query, which is expected to do something easy, really sucks.

Reason 2: blocking scenarios

Some scenarios will become impossible or nearly impossible. For example, what if you want to know which tag was assigned by which user? This is a reasonable requirement; after all, if the same user is assigning intentionally wrong tags to photos, you would certainly want to block this user.

Reason 3: waste of space

Not only do you have to write complicated queries, but you are also wasting the disk space by storing the same tags again and again.

Microsoft SQL Server is good at optimizing duplicate rows (see below), but this applies only to the same field. If one field is sky nature mountains and the other is river mountains nature, "nature" and "mountains" will waste 30 bytes instead of 15. Even worse, if one photo is tagged animal cat and another one, cat animal, Microsoft SQL Server will consider those fields different.

2. three-tables vs. two-tables approach

Some people may suggest a three table approach: one for pictures, another for tags and a third one which links tags to pictures.

It is a perfectly valid generic solution, and it's how teachers will tell you to do in college. The fact is, it's a bad solution in most cases when it comes to Microsoft SQL Server.

Microsoft SQL Server supports prefix and dictionary compression, which means that you can store the same value several times, without wasting more disk space. This can simplify a lot the three tables design without loosing neither the flexibility (which is lost in the one table scenario), nor the optimal disk space used by the data.

With two tables, most queries are easier and shorter to write. For example consider listing the first one thousand pictures with their respective tags. With three tables, you have to do two joins. With two tables, one join is enough.

Consider now a very simple scenario: you're displaying the picture with the list of tags. With the first approach, you have to search the TagPhoto table, then Tag table to get the exact tags. With the second one, you get them through a single select query with no joins at all (as noted by Catcall below, this does not apply if you use natural keys instead of surrogate keys).

Performance-wise, two tables approach was always a success for me when I profiled my applications on my hardware. This means what it means, and maybe in your case things will be completely different. In all cases, Microsoft SQL Server row compression is mature enough to be used, and was done especially for cases like that.

---

^{¹ In other words, a tag is a modifiable and "referenceable" entity: you can add a single tag, remove it, change it, affect it to another picture, etc. Since it is an entity like this, you have to store it in its own field in a database.
You can note that there are other entities which are stored combined in a same field. For example a comment of a user on a blog can be represented as a set of paragraphs, every paragraph being an entity. This is not the same thing. Paragraphs are not modifiable as separate entities: the user modifies the whole comment, not the paragraphs. Paragraphs are neither "referenceable", or at least they don't have to be: all paragraphs of a message must be displayed every time; all paragraphs of a message belong to the same user, have the same submission or last edit date, etc.}

Answer 2

Well, tags-content is actually an m-n-relationship. So normally (pun intended) you'd have one table containing the tags, one table containing the data (pictures for example) and one table for their relationship. Ideally you'll put an index on the tags in that table, because you're likely to search by tag more often.

To really scale out such models, you then might consider sharding. Or caching searches. Or maybe using a different database model optimized for that very use case. But I suppose 3-4000 pictures is not much data to worry about.

Answer 3

Case 1: A user may enter 1 tag value per picture and Pictures are stored in database or using the file system.

Suggested design for case 1: Store tags in a database table and use the file name as an Foreign Key.

Case 2: A user may enter 1 or more tag values per picture and Pictures are stored in database or using the file system.

Suggested design for case 2: Store tags in a database table Store file names of pictures in another table. Create a 3rd table (an intersection table) with at least 2 columns (first column is a foreign key of the picture file name, second column is a foreign key for the tag value)

Alternatively,

If your database has full text search, you will need only 1 table as in case 1.

In all cases, its a good idea to not store pictures in the database if the number of pictures does not violate your server's OS capacities.

I can explain further if you like.

Answer 4

This depends on how much control you want over your tags. Some sights are not sure how tags are going to be created and used, so they just keep it open. Your single field approach would handle this as would just haveing a single tags table joined to your image table.

A three table/Many-to-many approach is going to allow you to have more control over the tags table. If you need to reword tags, this is very easy. What you gain in control over these values, you'll lose in performance. The needs for your application should consider how much flexibility you want to give users.