CODEWITHSUNDEEP
mysqlsqldatabase
Sijo Jose
Sijo Jose

Reputation: 363

SQL column with multiple values

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.

Upvotes: 11

Views: 17951

Answers (2)

juergen d
juergen d

Reputation: 204746

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'

Upvotes: 18

MarcM
MarcM

Reputation: 2251

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.

Upvotes: 1

Related Questions