CODEWITHSUNDEEP
mysql
Boris
Boris

Reputation: 749

How to structure a DB to store different value

The title could be not very precise but here is what i would like to do, and to ask for advice

I have two tables, one is dedicated to different artists, the other one for different music genres. The final idea is to assign one or more genres to one artist.

I was thinking to use tbl_genre.ID and to store it in tbl_artist.genre but this is not going to work (possible issues when searching for a specific genre in tbl_artist using LIKE % %).

The other option is maybe to create a 3th table and to store all the genre IDs relevant to an artist in a separate row, but somehow this solution looks "lame", and im wondering if there is a better one.

Thanks.

Upvotes: 3

Views: 62

Answers (3)

Roland Mai
Roland Mai

Reputation: 31077

A map table is a pretty good solution (what you called "lame"). Another solution that is more denormalized is to store all the IDs of the genres in the artist table in a table field. Use a standard format such as JSON to serialize and de-serialize this field.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Your "lame" solution is correct. You should create a table with ArtistID and GenreID and add one row to this table for each genre that an artist belongs to.

Upvotes: 2

rwilliams
rwilliams

Reputation: 21497

This is a very typical many to many relationship. Genre's have many artists and artists can have many Genre's. You need to use a third 'join' table to accomplish this. I would call it something like tbl_artists_genres and it will have just two fields, artist_id and genre_id.

Upvotes: 5

Related Questions