Search code examples
mysqlsqlmany-to-many

Movie Database multiple genres

I'm trying to build a movie database Below is a selection from the "Movies" table:

id title genre
1 Venom Action, Adventure,Thriller
2 DeadPool Action, Comedy

I heard that I have to use model called a "many-to-many" relationship in the genre column, And it's not a good practice to use comma delimiter when modelling "real world" categorisations.

I want to know why it is not a good practice.

Solution

  • You seem to know that the right solution is a table with one row per movie and per genre.

    Here are some reasons why storing values in a string are a bad idea:

    • SQL has poor string processing functionality, so such a query is hard to optimize.
    • A column should contain a single value.
    • It is quite troublesome to validate the values in the column (i.e. you cannot create a foreign key relationship).
    • Simple queries -- such as getting a list of the genres or counting the number of genres on a movie -- are more tricky than necessary.
    • Genre names cannot contain commas (which might be tricky to enforce as time goes by).
    • Removing a value or adding a new value is tricky -- particularly if you want to avoid duplicates.