-3

I want to make a movie database and store the details of the movie like its release date, director, genre and other things. A movie can have multiple genres and even multiple directors sometimes. and the number of genres of movies aren't always the same for example a movie can be in a single genre while another one being in 2 genres, another one being in 5 and so on, so I can't specify the number of values in each movie's row and I need to have a limitless database where I can store as many genres as I want.

Is it possible? and if it is how can I make a limitless database to store multiple values in a field?

Shadow
  • 33,525
  • 10
  • 51
  • 64
AnotherRandomUser
  • 119
  • 5
  • 4
    You are talking about a many-to-many relationship between the entities "movie" and "genre". In relational modeling, you typically add a third table "movie_genre" to model all possible mappings. – The Impaler Oct 03 '22 at 03:50
  • You must have the concept of two different movies with the same name. Perhaps the year may suffice? – Rick James Oct 03 '22 at 03:53
  • What is your 1 specific researched non-duplicate question re how/why you are 1st stuck in what published presentation of what design method given what? Right now you are essentially asking us to (re)write a textbook with bespoke tutorial with no details on what you misunderstand or do or don't understand. [ask] [Help] Basic questions are faqs. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [homework](https://meta.stackoverflow.com/q/334822/3404097) [“help me"](https://meta.stackoverflow.com/q/284236/3404097) – philipxy Oct 03 '22 at 06:19

1 Answers1

1

It is possible to have a column for genres such that some rows may have "action, adventure, drama" while others could have "comedy, romantic". However, as mentioned in the comments, it is better to add a third table. Relational databases should be at least in a 3rd normal form. Check out this link for further reading.

The best way to design a relational database if you want to account for multiple genres and directors would be to include 2 additional tables. One, as suggested in the comments, contains at least two fields: movie_id, genre_id. The movie_id would link to the movies table and the genre_id would like to a genre table that lists all the genres you want to have stored in the database. Together the two fields would be a composite primary key for the movie_genre table.

The other table, movie_director, would follow the same pattern as movie_genre but have movie_id and director_id instead. The director_id would link to a directors table where all the director details are stored.

KatzFoote
  • 11
  • 2