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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Warning: the question being tagged
There are three approaches for storing tags:
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)
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.
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.