CODEWITHSUNDEEP
phpmysql
sehummel
sehummel

Reputation: 5568

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 statement look like if I wanted to retrieve all of the genres for a given album?

Upvotes: 0

Views: 1620

Answers (5)

Tony Casale
Tony Casale

Reputation: 1537

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.

Upvotes: 2

Czechnology
Czechnology

Reputation: 15012

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839124

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.

Upvotes: 2

John Parker
John Parker

Reputation: 54445

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.

Upvotes: 2

Felix Kling
Felix Kling

Reputation: 817128

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

Upvotes: 4

Related Questions