Hi.
I am having 3 tables namely
1. Actor {fields : ActorId, ActorName, Sex, DOB, Bio}
2. Movie {fields : MovieId, Name, YearRelease, Plot, PosterUrl}
3. Producer {fields : ProducerId, Name, Sex, DOB, Bio}
Relationships between tables are as follows:
Actor-Movie : Many-to-many {means, a actor can have more than one movies and vice-versa}
Movie->Producer : One-to-one {means, a movie can have only one producer}
Producer->Movie : One-to-many {means, a producer can have more than one movies}
Among these relationships i am having difficulties in Many-to-many relationship. i.e. i am unable to assign a movie more than one actors (using "create" view, i want to use
ListBox for displaying the Actors and selecting multiple values from it). Rest of the code (program) is running quite okay.
Just a note, a movie doesn't necessarily only have one producer. One to one relationships are more rare than you might think in many cases.
But as this is a question on the many-to-many relationships, let's move on.
You need an "Association Table". I'd name it MovieActor or ActorMovie (whichever makes most sense to you). It will consist of 3 fields
id - primary key, unique, etc
actorId - foreign key to actor.id
movieId - foreign key to movie.id
If you agree that a movie could have many producers, then you will implement a MovieProducer table the same way. Otherwise, if you think a movie should only have one producer, then add a foreign key producerId column to your movie table.
Now the relationships are established. Depending on your framework, accessing these lists are going to be performed differently. In SQL, you'd create joins. In Entity or Linq, you'd reference them as properties of the respective objects.
"Dream as if you'll live forever, live as if you'll die today." --James Dean
Thanks a lot for the reply and your suggestion, but in my requirement a movie can have only a single producer. That's why i had clearly mentioned.
and further moving ahead as you suggested, using a association table, let's say ActorMovie.... can you please provide me an idea or bunch of code for using the three :
'ActorMovie' , 'Actor' and 'Movie' tables altogether for defining CRUD functionality..??
Actualy i'm a new bie to MVC framework that's why having problems in implementation, even if i'm having some sort of logics. So expecting support from all of you. Thanks again.
AceCorban
Just a note, a movie doesn't necessarily only have one producer. One to one relationships are more rare than you might think in many cases.
But as this is a question on the many-to-many relationships, let's move on.
You need an "Association Table". I'd name it MovieActor or ActorMovie (whichever makes most sense to you). It will consist of 3 fields
id - primary key, unique, etc
actorId - foreign key to actor.id
movieId - foreign key to movie.id
If you agree that a movie could have many producers, then you will implement a MovieProducer table the same way. Otherwise, if you think a movie should only have one producer, then add a foreign key producerId column to your movie table.
Now the relationships are established. Depending on your framework, accessing these lists are going to be performed differently. In SQL, you'd create joins. In Entity or Linq, you'd reference them as properties of the respective objects.
Yeah, I'm not very familiar with Entity, so at best I can only provide some conceptual thoughts on how to perform this. Since you said you have CRUD operations working on your other 3 tables, however, you probably know how to do most of this. There are
just some basic things to keep in mind.
When performing a DELETE operation on Movie.
Iterate through each MovieActor and delete each instance before deleting the Movie object.
When performing an Add operation on Movie.
Since you said that you are operating with the assumption of only one Producer, then all you will do is add a DropDownList or some other form of selector on your View that allows the user to select from a list of producers. When you create the movie object,
assign its producerId based on the user's selection. You could give users the ability to add Actors in the add view if you wish, that is explained on the next section. Typically, however, I allow admins to create objects before giving that object children.
Adding MovieActors
After the admin has added a movie, they will be able to modify the movie. Within this view, present them with some control that allows them to select none, one or many actors. Update your movie object like normal. In addition, remove all instances of
that movie's MovieActor, then add a new MovieActor instance for each actorId selected by the user. This can be the more tricky of operations because you'll have to ensure that the modify view populates controls for existing Actors and allows the admin to
delete actors from that spot.
Removing MovieActors
The hardest part is ensuring the controls are properly created in the ModifyMovie view. On each delete, just remove the MovieActor instance based on its own primary key. Alternatively, you can use the movie id/actor id combo to select the appropriate MovieActor
to delete.
I'm sorry I can't give you actual code, but hopefully you can get it working.
"Dream as if you'll live forever, live as if you'll die today." --James Dean
anurag2090
0 Points
3 Posts
Many-to-many relationship
Aug 03, 2012 05:13 PM|LINK
Hi.
I am having 3 tables namely
1. Actor {fields : ActorId, ActorName, Sex, DOB, Bio}
2. Movie {fields : MovieId, Name, YearRelease, Plot, PosterUrl}
3. Producer {fields : ProducerId, Name, Sex, DOB, Bio}
Relationships between tables are as follows:
Actor-Movie : Many-to-many {means, a actor can have more than one movies and vice-versa}
Movie->Producer : One-to-one {means, a movie can have only one producer}
Producer->Movie : One-to-many {means, a producer can have more than one movies}
Among these relationships i am having difficulties in Many-to-many relationship. i.e. i am unable to assign a movie more than one actors (using "create" view, i want to use ListBox for displaying the Actors and selecting multiple values from it). Rest of the code (program) is running quite okay.
Can anyone please help me..........
AceCorban
Star
12318 Points
2269 Posts
Re: Many-to-many relationship
Aug 03, 2012 05:28 PM|LINK
Just a note, a movie doesn't necessarily only have one producer. One to one relationships are more rare than you might think in many cases.
But as this is a question on the many-to-many relationships, let's move on.
You need an "Association Table". I'd name it MovieActor or ActorMovie (whichever makes most sense to you). It will consist of 3 fields
id - primary key, unique, etc
actorId - foreign key to actor.id
movieId - foreign key to movie.id
If you agree that a movie could have many producers, then you will implement a MovieProducer table the same way. Otherwise, if you think a movie should only have one producer, then add a foreign key producerId column to your movie table.
Now the relationships are established. Depending on your framework, accessing these lists are going to be performed differently. In SQL, you'd create joins. In Entity or Linq, you'd reference them as properties of the respective objects.
anurag2090
0 Points
3 Posts
Re: Many-to-many relationship
Aug 03, 2012 06:29 PM|LINK
Thanks a lot for the reply and your suggestion, but in my requirement a movie can have only a single producer. That's why i had clearly mentioned.
and further moving ahead as you suggested, using a association table, let's say ActorMovie.... can you please provide me an idea or bunch of code for using the three : 'ActorMovie' , 'Actor' and 'Movie' tables altogether for defining CRUD functionality..??
Actualy i'm a new bie to MVC framework that's why having problems in implementation, even if i'm having some sort of logics. So expecting support from all of you. Thanks again.
AceCorban
Star
12318 Points
2269 Posts
Re: Many-to-many relationship
Aug 03, 2012 06:36 PM|LINK
Not sure which code to provide because I'm not sure which framework you are using for your datamodel. Are you using entity, linq, ado?
anurag2090
0 Points
3 Posts
Re: Many-to-many relationship
Aug 03, 2012 06:39 PM|LINK
I am using .NET MVC 3, Entity Framework 4.1 Code First approach and MS SQL server 2008.
AceCorban
Star
12318 Points
2269 Posts
Re: Many-to-many relationship
Aug 03, 2012 06:50 PM|LINK
Yeah, I'm not very familiar with Entity, so at best I can only provide some conceptual thoughts on how to perform this. Since you said you have CRUD operations working on your other 3 tables, however, you probably know how to do most of this. There are just some basic things to keep in mind.
When performing a DELETE operation on Movie.
Iterate through each MovieActor and delete each instance before deleting the Movie object.
When performing an Add operation on Movie.
Since you said that you are operating with the assumption of only one Producer, then all you will do is add a DropDownList or some other form of selector on your View that allows the user to select from a list of producers. When you create the movie object, assign its producerId based on the user's selection. You could give users the ability to add Actors in the add view if you wish, that is explained on the next section. Typically, however, I allow admins to create objects before giving that object children.
Adding MovieActors
After the admin has added a movie, they will be able to modify the movie. Within this view, present them with some control that allows them to select none, one or many actors. Update your movie object like normal. In addition, remove all instances of that movie's MovieActor, then add a new MovieActor instance for each actorId selected by the user. This can be the more tricky of operations because you'll have to ensure that the modify view populates controls for existing Actors and allows the admin to delete actors from that spot.
Removing MovieActors
The hardest part is ensuring the controls are properly created in the ModifyMovie view. On each delete, just remove the MovieActor instance based on its own primary key. Alternatively, you can use the movie id/actor id combo to select the appropriate MovieActor to delete.
I'm sorry I can't give you actual code, but hopefully you can get it working.