Suppose I have three tables Actor, Movie, Producer with following attributes

Actor(Id,ActorName,Bio)

Producer(Id,ProducerName,Bio)

Movie(Id,Name,Description,ActorName,ProducerName)

There can be many actors in a movie and actor can act many movies, same goes with producer. How to effectively decompose/create new tables to store data. I tried creating a new table with following schema

ActorMovie(Id, MovieId, ActorId)

but failed as there can be multiple actors in a movie.

CodePudding user response：

I think I have a solution for you. As I understand from your description, your table should be look like below.
Actor(Id,ActorName,Bio);
Producer(Id,ProducerName,Bio);
Movie(Id,Name,Description);
ActorMovie(Id, MovieId, ActorId);
ProducerMovie(Id,MovieId, ProducerId);

And Relationship Diagram should be look like this.

Note: Symbols of relationship might not be correct but connections should look like above.