0

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.

Dharman
  • 26,923
  • 21
  • 73
  • 125
Emma Grove
  • 138
  • 1
  • 10

1 Answers1

4

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.
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709