0

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.

nihar
  • 43
  • 5
  • You said your `ActorMovie` table failed, but what do you mean by that? It's the right solution, which becomes more apparent if you lose the implementation-detail-based table name `ActorMovie` and instead call it `Cast`, because the set of actors in a movie is called the cast! :) The `id` column on this table is superfluous. The primary key is just the combination of `MovieId` and `ActorId`, *unless* you want to be able to support movies where one actor plays multiple roles, in which case you would need to add a `role` column to the table, and it would then include the role in the key. – allmhuran Sep 24 '21 at 04:40
  • @nihar Please check my answer and let me know if it does not serve your purpose. Best wishes. – Srijon Chakraborty Sep 24 '21 at 04:44
  • Sorry for that naming convention, english is not my first language, but if I create that table, we can have multiple actors in a single movie so there data might become redundant right? is it possible to eliminate this redundancy? Suppose I send a post call to db to add movie, how i need to make my model look like? – nihar Sep 24 '21 at 04:46
  • Yes, there can be multiple rows that have the same value for `MovieId` in the `Cast` table, but that's OK. If I read between the lines here, I think you've got yourself into the belief that if a column in a table has the same value on different rows, then that's a normal form violation, and I can sort of see why people might think this. But that's not right, and the example you are using here demonstrates why that is. Each row in the Cast table is a unique combination of movie and actor, even though there can be multiple rows in the table with same movie. That's perfectly fine. – allmhuran Sep 24 '21 at 05:06

1 Answers1

2

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. enter image description here

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

Srijon Chakraborty
  • 2,007
  • 2
  • 7
  • 20
  • In the ActorMovie table, I can have movie with multiple actors right, so how to handle that? – nihar Sep 24 '21 at 04:44
  • @nihar Can you explain with some sample data or some images? – Srijon Chakraborty Sep 24 '21 at 04:45
  • Yes @nihar . I can store different actor of different movies in actor movie table. – Srijon Chakraborty Sep 24 '21 at 04:47
  • Suppose there is a movie Avenge, it has actors rob,mike,john so the table must look like this right (1 34 2) (2 34 21) (3 34 98) So how can I frame a model for rest api call? – nihar Sep 24 '21 at 04:48
  • @nihar yes. but mentioned about redundant information. Which one you refer as redundant information. – Srijon Chakraborty Sep 24 '21 at 04:51
  • I am unable to frame a model based on the above decomposition, like if I add data in movie table, I need to insert the same into ActorMovie and ProducerMovie right? How can i frame a model and insert in both of those while inserting into movie table – nihar Sep 24 '21 at 04:55
  • @nihar your movie should have List of Producers and List of Actors. So, First insert The movie information the insert ActorMovie (List of Actors) then ProducerMovie (List of Producers). – Srijon Chakraborty Sep 24 '21 at 05:00