Using set with values from a table

Question

I'm writing a database of all DVDs I have at home. One of the fields, actors, I would like it to be a set of values from an other table, which is storing actors. So for every film I want to store a list of actors, all of which selected from a list of actors, taken from a different table.

Is it possible? How do I do this? It would be a set of foreign keys basically.

I'm using a MySQL database for a Django application (python), so any hint in SQL or Python would be much appreciated.

I hope the question is clear, many thanks.

Answer 1

Make your tables like this:

Movies
MovieID   int auto increment/identity PK
MovieTitle
MovieDescription
etc...

Actors
ActorID  int auto increment/identity PK
ActorName
DateOfBirth
etc...

MovieActors
MovieID      PK  and FK
ActorID      PK  and FK
RoleName
etc...

Don't store multiple Actors within a single field within the Movies table, break them out into individual rows in the MovieActors table.

If you combine all the actors within one field, it will make writing queries difficult. For example, you would need to do string manipulation to query all movies that actor X was in.

Answer 2

The answer is clear too. You will need not a field, but another films_actors table. This table would act as your field, but much more reliable. This is called many-to-many relation.