0

I am building an sql movie database for all the dvds and blurays I own. I currently have the following tables...

movies(id(primary key), title, year, format, runtime) people(id(primary key), gender, dob, dod, name) credits(person_id(foreign key), movie_id(foreign key), job, role) genres(id(primary key), genre) movie_genres(movie_id(foreign key), genre_id(foreign key))

My question is do I need/is there a logical benefit i.e. memory management etc. to having 2 separate tables for the genres or would it be better to just have 1 say like....

genres(movie_id(foreign key), genres)

Robert Moon
  • 11
  • 1

2 Answers2

0

The movies_genres table is a mapping table to create a many to many relationship. That way you can have many movies associated with many genres and vice versa.

BonzoFestoon
  • 181
  • 1
  • 2
  • 12
  • Is this that advantageous over a one to many as I described? It just seems redundant to me to have 2 tables where 1 would suffice. – Robert Moon Dec 02 '21 at 23:04
  • If im understanding correct an example of many to many would be something like this.... Genres 10 | "action" 11 | "scifi" 12 | "thriller" Movie_genres 267 | 10 267 | 11 267 | 12 Vs a one to many like this... Genres 267 | "action" 267 | "scifi" 267 | "thriller" – Robert Moon Dec 02 '21 at 23:09
  • An example of using a mapping table like this enables you to link movie 1 to genres A, B, and C; movie 2 to genres B, C, F. Flip that around and genre B is associated to movies 1 and 2. In other words, a movie can be associated with as many genres as needed and a genre can be associated with as many movies as needed. That cannot happen with just one table. With one table you will limited to either associating one genre to a movie or one movie to a genre. – BonzoFestoon Dec 03 '21 at 12:51
0

Your final suggestion of using genres(movie_id(foreign key), genres) is probably the worst option of all as you have an additional join to get to a column which could just as easily be stored in the movies table. That really would be overhead for the sake of overhead.

If you are determined ​to drop the many-to-many you could store the genres directly in your movies table but a comma separated list of string values for genres would be very inefficient in terms of storage and querying. You could store the list of genres in a SET column in the movies table. This would use a fraction of the storage and can be queried very efficiently.

Both the many-to-many and SET options are widely used for this specific use case. I would opt for the many-to-many as it is the normalised form and easier to manage.

user1191247
  • 10,808
  • 2
  • 22
  • 32
  • Can you please explain how that would require an additional join? I was thinking it would be one less since its only joining 2 tables instead of 3? Or are you saying at that point its no different than just a comma separated list in the movies table? – Robert Moon Dec 03 '21 at 15:21
  • It would be additional in that there would be no benefit over it being stored directly in the movies table (as it is a one-to-one relationship). Whether in your proposed separate table or the movies table, a comma separated list of values is bad news unless it is never going to be used as a search criterion. – user1191247 Dec 03 '21 at 15:36
  • Here's a link to a previous post on this - https://stackoverflow.com/questions/17520720/movie-database-storing-multiple-genres/17520722 – user1191247 Dec 03 '21 at 15:42
  • Ok that makes sense. Thank you – Robert Moon Dec 03 '21 at 19:50