Database design question: Actor and Films database

[PingSpike]

So I have tblActor with a 1-to-many relationship to tblActorAlias. There is a junction table for the many-to-many relationship between tblActor and tblFilm. In addition to the primary keys it contains a job field (like director, actor, whatever) I'd like to also be able to store what Alias the actor used in this particular film here. However, I already have that information elsewhere.

The solution would seem to be to store the Alias used in the junction table only and do away with the tblActorAlias...except that would result in tons of repeated entries and I wouldn't be able to store known aliases for an actor unless they had used alias that in a film.

Is the best design here then to just enter an additional field in the junction table "AliasUsed" and prevent entry by the user of anything other then a selection of known aliases?

I'm just using MS Access btw.

 

[EagleKeeper]

If the Alias can be used in more than one film, then create a join table from Alias to Film.

Otherwise put in a link within the Alians table to the Film

 

[KLin]

Originally posted by: PingSpike

Is the best design here then to just enter an additional field in the junction table "AliasUsed" and prevent entry by the user of anything other then a selection of known aliases?

I'm just using MS Access btw.

Yes.

It's best to keep tblActorAlias since that's a child table of tblActor. This way you only have to store one piece of information(aliasID) for a film in the many to many table and can reference back to the alias AND then the actor.

 

[PingSpike]

Thanks for the replies.

Are you guys suggesting I make an actor table related one to many to an alias table, then relate the alias table many to many to the film table? Right now I have the actor table related directly many to many to the film table and alias is just a child one to many table from actor. Or do you mean the same as I have now with another relationship from the alias table also the same junction/join actor-film table?

If you're suggesting the first one, what if I wanted to record other attributes about the actor in each film other then their alias in a the same manner? Say they have a pool of different jobs they do but I also want to identify which one they performed in this particular film.

 

[KLin]

Originally posted by: PingSpike
Thanks for the replies.

Are you guys suggesting I make an actor table related one to many to an alias table, then relate the alias table many to many to the film table? Right now I have the actor table related directly many to many to the film table and alias is just a child one to many table from actor. Or do you mean the same as I have now with another relationship from the alias table also the same junction/join actor-film table?

If you're suggesting the first one, what if I wanted to record other attributes about the actor in each film other then their alias in a the same manner? Say they have a pool of different jobs they do but I also want to identify which one they performed in this particular film.

Set it up the same way. Make a table called tblActorJobs so you can setup multiple jobs for each actor. Then in your many to many table, add a column to select what job the actor performed for a film.

 

[PingSpike]

NVM, I was just confusing myself needlessly. I think I understand now....thanks again!