SQL column with multiple values

Question

i want to know how can we store multiple values in a sql column, or which is the best way to store the values in a sql column.

Consider in a movie table, a movie will be having multiple Genre
eg,
Genre: "Action, Adventure, Fantasy"
Genre: "Adventure, Drama, Fantasy, Thriller"

which is the best way to store the Genre values in database.

Answer 1

This is a classic n to m relation. It works like this

movies table
------------
id
name
...


genres table
------------
id
name


movie_genres table
------------------
movie_id
genre_id

The movie_genres table then contains one record for each genre of a movie. Example:

movie_genres
------------
movie_id  |  genre_id
1         |  1
1         |  2
1         |  13

To get the genres of a movie do:

select g.name as genre_name
from genres g
join movie_genres mg on mg.genre_id = g.id
join movies m on m.id = mg.movie_id
where m.name = 'star wars'

Answer 2

IMO, using a n:m relationship, as juergen_d suggested, is the best option.

But in mysql there is another option it might work in your case: using SET data type. Details here. Defintely not as powerful nor robust as using n:m relationship. Not normalization friendly.