Database design - which is the best data type?

[Yesideez]

Designing a database and although I've made and redesigned loads in the past I've come across a small problem.

 

I need to create a table (ie. "genres") listing a load of music types (ie. folk, rock, pop etc.) which will be referenced via an albums table. Now an album can be more than one genre (ie. folk rock) and I'm wondering what would be the best way to go about it?

 

I was thinking of using a sort of bit definition:

1 Folk

2 Rock

4 Pop

8 Indie

16 Dance

 

So "folk rock" would be 3 (1+2) but not sure how to go about it as I've never needed to use anything like this before.

[rhodesa]

that is one way to do it, but as referenced in the link below, bitwise operations use BIGINT which maxes out at 2^64. This means you can only have 64 genres.

http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

 

The normal way of doing this is to have a third table that links albums to genres. So it would be called (for instance) album_genre_link and would have two columns, album_id and genre_id

[Yesideez]

While reading up on the BIT data type I found a site listing reasons why not to use it.

http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql

 

I checked out the SET data type then found out that the fields have to be defined upon database creation - I need the contents to be fluid so SET is no good.

[mrmitch]

I'd do something similar to rhodesa, except the lookup table would be albumid, genre and genre would be an enum with your categories.

 

You can have the album listed in there as id 1, genre Folk and again as id 1, genre rock.