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.
5
-
this is probably better fit dba.stackexchange.com– Lie RyanSep 11, 2011 at 12:27
-
1After writing my answer, I noticed the "The database will have over 3-4000 pictures". With such a tiny number of pictures, it doesn't matter too much. Efficiency-wise, I would just pull the entire list of tags when web app starts, store this in a cache, and avoid querying the database later.– Arseni MourzenkoSep 11, 2011 at 13:11
-
At what point would the number of pictures start to affect it? Are we talking hundreds of thousands of pictures before there are any noticeable problems?– Brian GreenSep 11, 2011 at 17:40
-
@Brian Green: let's say you have an average of 5 tags per photo, and 6 letters per tag. With 4 000 pictures, it gives 120 000 letters, i.e. 120 KB. You can easily hold 120 KB in memory of your application. Now, at what point problems will appear? Hard to say. Probably you should think about it when you'll see the first signs of the problem.– Arseni MourzenkoSep 11, 2011 at 20:10
-
A "proper" design would indeed use another table; you really don't want to introduce table scans. But as MainMa said: you'd do want to do the heavy lifting in memory. Even when you scale out, the top 1000 tags will still cover 99.9% of your photo's.– MSaltersSep 12, 2011 at 8:14
Add a comment
|
4 Answers
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
nvarcharfield, - 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:
- 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
skyand 432 images are taggedriver,skywill appear beforeriver). 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.
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.
answered Sep 11, 2011 at 12:40
-
I am not sure how would row compression change the schema so that M-M relationship be avoided. Also, what is exactly wrong with the nvarchar datatype?– NoChanceSep 11, 2011 at 13:42
-
1+1 for point 2. Every time I've seen varchars used to store a list of items that needs to be individually searchable, it's turned into a mess. Sep 11, 2011 at 19:18
-
1With three tables, you have to do two joins if you use surrogate keys. If you use natural keys, you don't need any joins. Sep 11, 2011 at 21:58
-
When you say, "Microsoft SQL Server row compression is mature enough to be used, and was done especially for cases like that", you seem to be saying that row compression was implemented especially to eliminate the need to use three tables for n:m relationships. Is that in fact what you're saying? Sep 11, 2011 at 21:59
-
@Catcall: no. What I want to say is that row compression allows to store duplicate values without caring too much about the wasted space, since those values will be stored only once. Sorry, I'm unable to reformulate my answer better. If somebody see a better formulation, feel free to edit the answer. Sep 11, 2011 at 22:10
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.
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.
-
Yea, I'm not storing the actual picture, just the file path to the picture. Sep 11, 2011 at 17:39
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.
