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
- 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.
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:
- 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
sky will appear before
river). Can you do that easily? Can you do that with just one simple query?
- 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.
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.