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
  • 30,962
  • 25
  • 85
  • 135
Emma Grove
  • 148
  • 2
  • 11

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,242,037
  • 58
  • 646
  • 786