sql - product with multiple category type database schema

Question

I want to store information about song files which they are categorized by:
1. genre for example: pop, rock, classical etc.
2. instrument used like piano violinn etc
3. Artist
Each song has many instruments. Each song has many artist.
So all of the relations are many-to-many. How can I implement this? Is it good idea to make many-to-many relation between song entity and those three category type entities? or should I only have a single category entity that inherits those sub categories?
thanks in advance

Answer 1

The instrument and the artist are both examples of "has-a" relationships. The genre is an example of an "is-a" relationship. Has-a and Is-a relationships are modeled quite differently in relational database design. A song is a pop song, or a song is a rock song. (I presume that a song can be both pop and rock, but that's not clear in your description).

Has-a relationships are more common, and are generally covered in any good tutorial. Is-a relationships are often not given enough coverage in those same tutorials. If you want to get some articles about ER modeling of is-a relationships, lookup "generalization/specialization". This will explain how to depict one of these relationships but not how to design tables to fit.

If you are familiar with object modeling, your easiest handle on the concept is known as class/subclass pattern aka type/subtype. This is straightforward, but it depends on built in support for inheritance. This support is generally present in any object system, and does most of the hard work for you. If you are designing a relational database you come face to face with the fact that the relational model does not directly support inheritance. This means that you have to design tables that mimic the benefits of inheritance, unless you are willing to use SQL extensions available in your dialect of SQL.

Two relatively standard designs are known by the names of these two tags: single-table-inheritance class-table-inheritance

You can get a brief description by reading the Info tab of each of these tags. You can get an even better description by looking up Martin Fowler's treatment of these two patterns.

The single table solution will help you create a single table for all song files, with columns that pertain to any or all of the categories.

The class table solution will help you design one table for the song files and one table for each genre. It will be helpful if you use the shared primary key technique and thereby cause each entry in a genre table to "inherit" its ID from the corresponding entry in the song file table. You have to make this inheritance happen by extra programming when you go to insert a new song file.

Which one is better? It depends on your case. You decide.