trying to trap a redundancy

[jreed72]

Trying to create a movie database project for school. requirement is 3 key tables plus a handful of look up tables. For the most part I have the design down

1st main table Movies

PK - MovieID
Movie name
movie year (many to 1 to yrlookup)
Movie genre (many to 1 to genre lookup)(combo box multichoice)
Movie rating (many to one to rating lookup)
format (many to one to format lookup)


2nd main table People

PK - personid
FK - movieid


first name
last name
comments

3rd main table table Cast/crew

PK - crewid
fk - personid
rolls played (many to one to roles lookup table)(combo box multi choice)
character name
comments

I have set up a main entry form with all movie fields and with subform of all cast and crew fields. I have placed first and last name with personid from people table in the cast and crew subform to give a name to people when entering casting rolls.
It works the way I want except for one problem! When entering an actors first and last name that is in more than one movie it creates a redundancy that I'm not happy with. This setup creates a new personid for the actor every new instance he is entered in the form.
I have played with several different combinations of relationships between 3 main forms, but always end up with the above problem...
Can anyone see the flaw in this setup and direct me to a better solution? Or should I just live with this redundancy? I guess what I am asking is it possible to have the actor maintain one pesonid in a case like this?
Hope this is clear enough to understand.
thanks you very much!

[ConneXionLost]

Hi jreed72,

I would suggest rearranging the tables/keys this way:

tblMovie (Parent table)
MovieID - (PK)
Movie name
movie year (many to 1 to yrlookup)
Movie genre (many to 1 to genre lookup)(combo box multichoice)
Movie rating (many to one to rating lookup)
format (many to one to format lookup)

tblPeople (Parent table)
PersonID - (PK)
first name
last name
comments

tblCrew (Child table)
CrewID - (PK)
MovieID - (FK)
PersonID - (FK)
roles played (many to one to roles lookup table)(combo box multi choice)
character name
comments

This should prevent any redundancies in tblMovie and tblPeople. Keeping the CrewID as a separate PK in tblCrew will also allow you to let one person play two (or more) roles in the same movie (actors love to direct). Another benefit will be the option of setting up a mainform/subform with tblMovie & tblCrew as well as the same thing for tblPeople & tblCrew.

Cheers,

[jreed72]

thank you I will give that a whirl and see

[jreed72]

Ok, I changed around relationships to what you suggested. Using the form I have already have It still assigns a new personID everytime I enter a actor of the same name. I have a feeling it is my Form design... As I mentioned I wanted to use one form to enter all the information I would need from all 3 tables. For example if I enter actor A in movie 1, then enter same actor A in movie 4.
I had it set up as move been main form with cast and crew as subform. But I also had inserted first and last name from people table into cast and crew sub form to "put a name" to what I was entering for casting information. Is this causing this issue maybe? Is there another way to do this using just one form?
Should I make up a lookup field of actors and go that route maybe?

[ConneXionLost]

I didn't realize you were attempting to do all this in one form. Ideally, you would set up one form per main entity. In this database you have two main entities (the parent tables). Have a look at the example to see how it might work the way I previously suggested.

Cheers,

[jreed72]

I was working with your model and though I had it going alright, but now when I try to reference an actor through the Cast and crew subform of Movies form it gives me an error. "The current field must match the join key ? in the table that serves as the 'one' side of a one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.
Makes no sense to why this is coming up... My relationships are identical to the example you showed me?

[jreed72]

I think I have now that a returned to the project when its not 1 am in the morning... I was linking the subforms to a table instead of a qry. I little fuzzy on why this caused problems, but the fact that it works now is the key point. thank you for your help! It was very much appreciated

[ConneXionLost]

Great news!

If you're ready to mark this thread solved, check the links in my sig.

Cheers,