Divide column into multiple tables

6

I want to save a set of information about movies in a database but I have a question, should I organize the genres in a column (example below).

Database: MySQL 5.6.21

Engine: innoDB

Or arrange the genres in a separate table and use a third table for relationships?

Using the 1st form I can select using LIKE.

    
asked by anonymous 01.04.2015 / 17:17

2 answers

7

I suggest using it this way:

So you can get full control of the movies by genre, how many action movies you have, how many action and comedy movies you have, using only JOIN .

    
01.04.2015 / 17:34
7

The two forms are possible. The most traditional is to use an extra table and normalize the registration of the genres. This is the normalized form. In general you need to have a good reason to not normalize .

In any of the solutions, the ideal is to have a table with the descriptions of the genres. And then you only use ID of them. In the column of Generos of your table Videos , or in the table of mooring of films X genres - the most recommended form.

If you still want to keep a list of genres in the column, you can see a question I did on how to index this.

You would probably have the tables:

Videos

Id
Titulo
Duracao

Generos

Id
Descricao

VideosXGeneros

Id
VideoId
GeneroId
    
01.04.2015 / 17:25