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

Answer 2

I answered a very similar question to this recently. (http://stackoverflow.com/questions/3675711)

Essentially, you need to completely separate all the items, so instead of saying that your movie contains actors, directors and genres - you would instead have a table that linked them together.

So you would have a movie table that had columns movie_id and movie_title and then a table to link a movie to an actor, something like MovieActors which had the columns movie_id and actor_id. The same for directors and genres.

Answer 3

Because an actor can appear in many movies, and a movie engages many actors, you need to implement a many-many relationship. The usual way of doing this is introduce a third table building the relationship. So:

Movie:: movie_id(primary key); movie_title;
Actor:: actor_id(primary key); actor_name; 
Appears_in:: movie_id(foreign key); actor_id(foreign_key)

This Appears_in table might have movie_id, actor_id as its primary key, and/or index both columns individually and jointly depending on the required accesses.

Answer 4

The other answers here are "correct", but there's something to note. Even if you implement the bridging tables, you'll need to do more. I.e. say there is a movie that had a certain director for a period of time, and then changed, and so on and so forth.

The point is, you need to determine the scope of your application (in this case and in general). What types of relationships will it handle? Once you decide that, then you can implement an appropriate structure (that is, your suggested structure may be "correct" for a very small-scoped project, but not for another type).

What I'm saying is, determining the scope can help you decide if your implementation is correct. Sit down and decied what, exactly, you're using this project for then take the appropriate route (which will probably be the relational tables).