Access Relationship Help!

[kafkat1]

Hi everyone!

I'm fairly new to Access and I'm having trouble with a database.
I need to create a database for a movie manager to keep track of movies, the actors in the movies, and the directors and producers needed.

I understand that:
-each movie can have multiple actors, but each actor can be in a movie only once
-each actor can be in multiple movies (this would be a one-to-many relationship, right?)
-each director can direct multiple movies
-each producer can produce multiple movies

I've tried setting up a few different relationships (and I've attached screenshots of them).
I think the relationship between actors and movies is a one to many, but the relationship from individual movie to individual actor is a one to one (which is what I tried creating in the second screenshot).

I was wondering if anyone could explain the relationships a bit to me?

 

[Alansidman]

This site may be helpful

http://www.databaseanswers.org/data_models/theater_company/index.htm

 

[VilaRestal]

Everything looks good except:

• MovieActor table needs both fields as primary keys (combined makes the primary key). This will stop a movie having the same actor twice - it would be a duplicate primary key in MovieActor table and Access won't allow that.
• Going by the second image which is almost right on the Actors side, join Movies table to MovieActor table on MovieID to MovieID (not ActorID to MovieID)
• Movies table doesn't want an ActorID field - movies have more than one Actors. Their actors are stored in the MovieActor table.
• You only need the many-to-many Movies-Directors and Movies-Produces if you need to allow for multiple Directors and multiple Producers for each movie (which I suppose is perhaps possible but I can't think of an example - actually yes I can: Monty Python's The Quest for the Holy Grail directed by Terry Gilliam and Terry Jones). So MovieProducer and MovieDirector would work in the same way as MovieActor. In which case you don;t want DirectorID or ProducerID in the Movie table either and the MovieDirectors table and MovieProducers table will both join to the Moviess table MovieID to MovieID.

I'd also recommend removing the spelling mistake on the Moviess table now before it gets too difficult to change it. Otherwise it will haunt you. You'll have to remember to spell it wrong everytime you write a query involving it

 

[RainLover]

At first your structure looks good. However if you want to get serious your structure is breaking Normalisation rules.

Think of this. What would you do if you needed to track the Sound Editor, Editor, Best Boy, Stunt Man1, Stunt Man2, and so on. The method you are using would require an extra Table or two for each. Further more this could only only be done by the programmer. Currently your positions of Director, Producer and Actor are Hard coded into your design.

If you had a table for movies. You would have,

First Table

tblMovie
MoviePK (Autonumber)
Title
StartDate
EndDate
etc

Second Table

tblPositions
PositionPK (Autonumber)
Position Text. Like Director, Producer etc. This field can grow over time to include Co-Star, Etc

Third Table

tblParticipants
ParticipantPk Autonumber
ParticipantName
Etc

tblJoin would be a Join Table for a Many to Many Relationship.


tblJoin

JoinPK
MovieFK to the First Table tblMovies
PositionFK to the second Table tblPositions
ParticipantsFK to the third table tblParticipants

A join table is not limited in the number of Fields. Two Foreign Keys plus a Primary Key is common however more Foreign Keys are acceptable.

EDIT

I changed some tables around as they were not correct..
Corrected Bad English