How do I store multiple values for a single item in a cell in MySQL

Is serialize the best way to do this?

If, say, I have a database of music and I want to classify music by genres. If I allow for multiple genres for a given album, do I store them as an array and serialize them? Like this?

$array = serialize(array(1,2,3)); // numbers are IDs of genres

I get that from another post here. I know I can unserialize them. What I don't get is how I would write an SQL statement that would retrieve some or all of the data. What would my SQL stateme开发者_C百科nt look like if I wanted to retrieve all of the genres for a given album?

It depends on what queries you want to run, but you should put the genres into its own table:

Genre
id | name

and create a table that associates albums with genres:

Album_Genre
album_id | genre_id

You really want to normalise the data and store the genres and the album to genre association in different tables.

i.e.: You'd ideally have three tables:

1. Albums The album data
2. Genres The genre data
3. Album -> Genre lookup The album id and genre id that forms each association

By doing this you'll be able to trivially add/remove, etc. genres as required in the future and will be able to perform lookups by joining the tables rather than having to perform string manipulation.

You should normalize your database using an association table.

The Wikipedia article on first normal form has a good example involving people with multiple phone numbers.

You're trying to model a many-to-many relationship (Music to Genre). So, you should create a separate table (Genre) and then a cross-reference table (Music_Genre) that has the IDs from the Music and Genre tables.

Don't.

For a many to many relationship, you need a third table:

songs: id, name, ...

genres: id, name, ...

relation_songs_genres: song_id, genre_id