1:Persons:-
Id
EntityTypeId
Name
Gender
DOB
BIO
2:Movies:-
Id
Name
Year of release
Plot
Poster
Foreign Key(ProducerId)
3:EntityType:-
EntityTypeId
Entity
4:-MovieActorsMap
Primary Key(Foreign Key(ActorId)
Foreign Key(MovieId))
Please correct me for any changes
I think you've done a good design overall.
Luckily you seem to be in a specific, limited situation so you don't need to consider things such as non-human actors (Lassie, for example) or multiple producers (in reality, movies can have more than one producer).
Despite the limitations, I would allow a person to be both an Actor and a Producer (as, for example, DiCaprio is irl). Plus I would design to allow for other categories to be added later, such as Director or Guest Star. Therefore:
(1) rather than putting "EntityTypeId" (a rather awkward name too, btw) in the Persons table, I would have a separate table with MovieId, PersonId and EntityTypeId (yes, all three, see #2 below for why)
(2) meaning instead of a Movie_Actor table, say a Movie_Persons table, which includes the EntityTypeId. That is, for each movie, specify whether the person was an actor or producer (or perhaps both!) for that specific movie, rather than pretend a given person could only be one or the other for every movie.
@ScottPletcher Thanks for the inputs. I updated according to inputs .i feel there is no need of Foreign Key(ProducerId)
Once again thank you very much
You're welcome.
Btw, you don't need a generated id in the "MoviePerson" table. Use the combination of ( MovieId, PersonId ) as the primary key.
sorry to bother you .I have one small question .I'm quite new to SQL and entity framework.I want to develop API's for only actors or only producers but we don't have any relation of person -> role
True, we don't. I can see where that would be needed.
You can add a PersonRole table to identity the role(s) a person can do.
what if we have a one to many relation for person and role like
Person { Icollection } having a roleid as foreign key is that a bad desgin
In formal terms, yes, that's a bad design.
First normal form (1NF) requires that each column value be a single value that isn't further broken down into separate values or columns.
for (i = 0; i < 1000; i++) {
Thank You Scott;
}
