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.