3

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.

Boris
  • 719
  • 7
  • 21

3 Answers3

5

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.

rwilliams
  • 21,188
  • 6
  • 49
  • 55
  • Very typical I would say. It's the second most common relation ship after one-to-many. – GolezTrol Sep 11 '11 at 20:13
  • Don't forget to create a composite primary key around artist_id and genre_id. – cdhowie Sep 11 '11 at 20:13
  • One more question, and if i user "zerofill" option on genre IDs, and store the IDs of the genre in a field in artist's table? – Boris Sep 11 '11 at 20:21
  • 3
    @Boris: ***"Is storing a comma separated list in a database column really that bad?"***: http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad – ypercubeᵀᴹ Sep 11 '11 at 20:24
  • @ypercube - so my "smart" solution is really bad, thanks man. – Boris Sep 11 '11 at 20:28
  • You identified one possible issue (searching) and you asked before starting working on the problem. Just search SO for questions regarding "comma separated" lists (or fields or columns) and you'll see too many have fallen into this (and spent lot of time and effort into making it to work until the issues arised). – ypercubeᵀᴹ Sep 11 '11 at 20:33
2

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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

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.

Candide
  • 30,469
  • 8
  • 53
  • 60