SQL for a movie database search system

Question

I am building up a database of movies. Each movie will have fields genre, actors, director etc. Now my question is how do I design a database and write SQL as each movies can have multiple actors or directors etc.

Right now my database design for table movie is:

• movie_id
• movie_title
• actors_id
• directors_id
• genre_id

But this way it seems harder to put in multiple actors in a movie.

Answer 1

You have a many-to-many relationship between actors and movies. Each actor can star in many movies and each movie has many actors. To model a many-to-many relationship in SQL you can use a junction table (also called a cross-reference table, among other names).

+-------------+
| Movie       |
+-------------+
| Id          |<----+
| Title       |     |
| Year        |     |
+-------------+     |
                    |
+-------------+     |
| Actor       |     |
+-------------+     |
| Id          |<-+  |
| Name        |  |  |
| Birthdate   |  |  |
+-------------+  |  |
                 |  |
+-------------+  |  |
| Movie_Actor |  |  |
+-------------+  |  |
| ActorId     |--+  |
| MovieId     |-----+
+-------------+

To query for the names of all the actors in the movie with ID 153:

SELECT Actor.Name
FROM Actor
JOIN Movie_Actor ON Movie_Actor.ActorId = Actor.Id
WHERE Movie_Actor.MovieId = 153