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)

CodePudding user response：

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.

CodePudding user response：

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.