How to design a database wherein multiple tags(string) are to be associated with an id?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.