Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have to design a database wherein I have to associate an audio_id with multiple tags(words). I am considering following approaches to select one from these:

1) To have multiple fields for multiple tags (columns: tag1, tag2, tag3.... tag10) corresponding to a single audio_id. The number of tags in my application will not be more than 10-15.

2) To save the tags(words) as comma separated single string corresponding to a single audio_id.

3) To save the associations (tag:audio_id) in a separate table. But the issue here is that the associations can be n to n. Multiple tags can be associated with an audio_id and same tag can be in multiple audio_id.

Also please let me know if there can be any alternate design for this scenario or its better to consider any other type of database other than MySQL.

Total number of tags will be around a million and audio_id are around a few thousands. I am concerned for the performance of the system.

share|improve this question

3 Answers 3

I'd suggest having one table for tags, one table for "audios", and a third table to keep track of which tag is associated with which audio. Something like:

tags_audios

audio_id   | tag_id
----------------------------
1          | 2
1          | 3
2          | 1
2          | 3
3          | 6

This is usually how I see m:n relationships stored, and it is pretty close to Solution #3 which you proposed.

Solution #1 will give problems when you decide you do want more than 15 tags. You will also have to search all of the tag columns when you want to find just one value.

I can't think of a good situation to use Solution #2, except maybe in a reporting system where aggregating all of the tags into a string will make the final report easier to generate.

share|improve this answer
    
+1, this is how I designed a generic taxonomy system for a project at work, and how you typically see other projects implement it, like wordpress taxonomies. –  birryree Nov 4 '11 at 16:48
2  
Normalization is the best way to go (almost always). In the above solution make sure you set your database Ref. Integrity constraints correctly. Ask what happens if I delete an audio_id, a tag_id or a row from the association table. –  Emmad Kareem Nov 4 '11 at 20:54
    
Thanks, this what I was actually thinking but was not able to decide with concrete reason. –  Venkatesh Nov 4 '11 at 22:45

It really depends on how you use the tags. If all you're doing is saving and displaying them, then a simple comma separated field might work best. If you're searching, sorting, and filtering on the tags, a 3-table arrangement (as already described) might work best.

You really need to figure out what the usage patterns will be. Once you've got that, you can figure out which table layout works best. (And of course, you need to balance disk space and performance in your decision.)

Running a few tests (with LARGE numbers of rows) may be necessary before you can accurately decide which model to go with.

share|improve this answer
    
Thanks, for the comment, I am looking for searching, sorting and filtering on the tags and there may be scenarios when I want to fetch all the tags associated to an audio in one select query. I was considering comma separated tags for that scenario only. –  Venkatesh Nov 4 '11 at 22:44

This would typically be done with a many-to-many relationship.

This would involve the following tables:

audio (id, tags, ...)
tag (id, name, ..?)
tag_audio (audio_id, tag_id)

Also, as you can see I included a "tags" field on the audio table. This would be a cached value (comma/semicolon/space delimeted) for times when you don't need 100% correct values. You could simply use this field, which would save on extra Joins to the database.

Secondly, as a best practice, table names should be sigular. You can adjust this example as you see fit, however.

share|improve this answer
    
Thanks for the reply. but if I take delimiter separated values in a single column in audio table, would this redundancy be good for the search. I think I have to use LIKE '%***%' in the search and that will lead to table scans. –  Venkatesh Nov 4 '11 at 22:48
    
@Venkatesh - The purpose of that cache column isn't really for searching or inserting. As a rule of thumb, consider that column to be 'read-only'. You would use this column for displaying tags (thus, avoiding joins), and would only update the values in this column on a set scheduled (ie, not automatically when a new tag is appended to the audio) to avoid locks when updating the row. –  Craige Nov 7 '11 at 14:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.