Relationship Help

Hello,

I'm having some problems setting this up right.

I have 3 databases.

Volunteers

FIRST
LAST NAME both primary

Movie Data

MOVIE
DATEboth primary

Attendance

MOVIE
DATE

USHER 1 First
Usher 1 LAST

USHER 2 FIRST
USHER 2 LAST

AUTOKEY PRIMARY

I have a one to many relationship between the movie and date between the attendance and movie data table. I have tried linking the name to the usher fields but I"m not getting the result I want. I want to be able to open up the volunteer table and have it show me the movies they have worked on. The movie data table will list who worked it, but the volunteer tables are not.

Any ideas?

Thanks,
Cody

Volunteer table
VolunteerID AutoNum (primary key)
Volunteer FirstName
Volunteer LastName

Movie Table
MovieID autonum (Primary key)
FkVolunteerID
Date

Attendance Table
AttendanceId Autonum (Primary key)
FkMovieId
UsherFirstname
UsherLastname

Moveid.fkVolunteerId = Volunteer.VolunteerID


Attendance.FkmovieId = Movie.MovieID

These can be linked in the relationship diagrammer

Smart said:

Volunteer table
VolunteerID AutoNum (primary key)
Volunteer FirstName
Volunteer LastName

Click to expand...

This could lead to a Volunteer being entered into the database more than once. Suggest making first and last name a combined unique index unless you can find a better PK

Movie Table
MovieID autonum (Primary key)
FkVolunteerID
Date

Click to expand...

This would mean that the MovieIDcan only be associated with one FkVolunteerID. Is this correct.?

Attendance Table
AttendanceId Autonum (Primary key)
FkMovieId
UsherFirstname
UsherLastname

Moveid.fkVolunteerId = Volunteer.VolunteerID

Click to expand...

So an usher could be associated with a MovieID more than once and the Usher First and last names could be entered incorrectly.

Not exactly sure h=what you are trying to achieve but suggest that you need to review your design carefully and normalise the data.

Initially to me it appears you should have a
tbl_Movie with Movie data
tbl_Volunteer with Volunteer data
tbl_Usher with Usher data

These table to have selected PK's not necessarily autonumbers.
Then define the relationhips that exist between these entities.

Thus linking relations can be created with referential integrity enforced.

Len

I see this a bit differently:

tblVolunteers
VolunteerID (PK autonumber)
VolFirst
VolLast

I would not use a unique Index on the combination of first and last since you could have volunteers with the same name.

tblMovies
MovieID (PK Autonumber)
MovieTitle
other info about the movie

tblShowings
ShowingID (PK Autonumber)
ShowWhen

tjxStaffing
StaffingID (PK Autonumber)
VolunteerID (FK)
ShowingID (FK)

With this structure you would have a main form bound to tblShowings and a subform bound to tjxStaffing linked on ShowingID. This would allow assigning volunteers to a showing and list all volunteers for a showing. To show all movies that a volunteer has worked would just involve a query. The thing is you have a one to many realtionships between movie and showing and a many to many relation between showing and volunteer. I don't think the other proposed solutions deal with that.

Scott

Take your point and accept what you are saying.

However using an Autonumber solely as the PK offers no protection from John Smith being entered twice when indeed they are the same person.

This does illustrate the difficulty and importance of choosing PK's carefully and/or using other tools to ensure data integrity.

Maybe its not a problem if you have say only 20 people but maybe the potential is compounded if you have multiple users.

Maybe regardless of anticipated number of entries we should always strive for best design so that we already have the best design habit when what we thought would be small suddenly escalates.

Len

Len Boorman said:

However using an Autonumber solely as the PK offers no protection from John Smith being entered twice when indeed they are the same person.

This does illustrate the difficulty and importance of choosing PK's carefully and/or using other tools to ensure data integrity.

Maybe its not a problem if you have say only 20 people but maybe the potential is compounded if you have multiple users.

Click to expand...

I would make selection of the names a combo box. I would make the adding of new names to the table a separate form with prompts that check the name against existing names for near matches.

Go along with what you say Neil. Question of being really aware of different solutions and the situations that may need to be addressed specifically

Len

Len Boorman said:

Scott

Take your point and accept what you are saying.

However using an Autonumber solely as the PK offers no protection from John Smith being entered twice when indeed they are the same person.

This does illustrate the difficulty and importance of choosing PK's carefully and/or using other tools to ensure data integrity.

Len

Click to expand...

True, but that's NOT the function of an autonumber. The autonumber's purpose is solely to provide a unique identifier for a record that can be used internally within the database app.

Insuring against duplication is a separate function. But using a unique index on first and last name is not the answer for the same reason that names are not a good PK. It is possible for 2 people to have the same name. Granted the smaller the population the less likelihood of this happening, but it is possible. I agree with Neil, that preventing duplication of people is a function of the form where the volunteer is entered. You would then do some data validation to warn (not prevent) of a potential duplication.

The autonumber I suggest is does not have the sole purpose as a unique identifier.
Primary Key Definition: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID). Primary keys may consist of a single attribute or multiple attributes in combination.

It is easy to take the autonumber and I am aware that there are two camps in terms of whether or not a Primary Key should be meaningful.

Personally I prefer to use a primary key that is wherever possible an attribute of the entity and provides the unique reference to that particular instance.

The arguement that either are acceptable is possibly true but the a PK that is also an attribute of the entity does make un-neccessary additional code to prevent duplication.

I have used autonumbers as PK's particularly within transaction logs but have also used them when needing to process datasets in sequence within applications associated with FMECA's, MSG3 analysis and also calculating Poisson Stocking Levels.

No doubt there are advantages and disadvantages to both views.

Len

Len,
As you say there are two camps in this debate of natural vs surrogate keys. A natural key is what you describe as an attribute of the entity. A surrogate is generated by the DBMS. I subscribe mostly to the surrgoate camp. I've read a few articles on the debate (like this: http://www.utteraccess.com/forums/showflat.php?&Number=637301).

You cite SSN as an example. But SSN is not guaranteed to be unique. Granted its rarely duplicated but it can happen. Using a natural key doesn't necessary preclude additional code to avoid duplication. Since a natural key is generated outside the database, you have no guarantee of uniqueness. You are relying on an external system to maintain the uniqueness as well as upon methods of data entry to not make errors. Problems that are eliminated by using a surrogate key.

Getting Closer

Scott,

I'm going with your method, and I think I'm getting close. Now, I need to beable to set up something for each movie that says, usher 1 name, usher 2 name, concession 1 name, ticket taker name..etc and link these to separate people. Sorry if these are dumb questions. I started using access about 1 week ago..good news is is that its covered in one of college courses.

Thanks,
Cody

codymartin said:

Scott,
I'm going with your method, and I think I'm getting close. Now, I need to beable to set up something for each movie that says, usher 1 name, usher 2 name, concession 1 name, ticket taker name..etc and link these to separate people. Sorry if these are dumb questions. I started using access about 1 week ago..good news is is that its covered in one of college courses.
Thanks,
Cody

Click to expand...

Then you will need to add to my method. You need a Positions lookup table:

tluPosition
PositionID (PKa Autonumber)
Position (First Usher, Second Usher, Ticket Take, First Concession, etc.)

Then change tjxStaffing and add PositionID as a foreign key.

From there you can list each person assigned to each position for each showing.