Relationships and table confusion

[amy3000]

I'm sure the answer is here somewhere but I'm not sure how to search for it.

I have a database of volunteers for a medical reserve corps in the county I work for. The "Person" table is the main table for the database and is organized:

"PERSON TABLE"
Person_ID (primary key)
Last_Name
First_Name
Middle_Name
Many other fields about the applicant.

All the other tables thus far are various attributes of the applicant, eg., the type of setting they have worked in. For these I have a tables like this:

"WORK SETTINGS"
Setting_ID (primary key)
Setting_Type (refers to lookup table)
Setting_Detail
Person_ID (foreign key)

Now I need to add a volunteer training information. At first I thought I'd set it up like the others but that didn't seem to make sense. If 10 people were in the training the data entry person would need to enter a record for each participant. It made more sense to me to have a training table/form and then enter each participant in a sub_form. But this is where I'm not getting my head around. I saw it like this:

"VOLUNTEER TRAINING"
Training_ID (primary key)
Training_Topic
Training_Date
Training_Location
Training_Hours

And here is where I'm not getting how to set it up.
"TRAINING PARTICIPANTS"
Participant_ID (lookup in "PERSON_TABLE", I want the whole name)
Training_ID

I want to be able to see the full name of the participants. When I use the "PERSON_TABLE" as the lookup, I can only save one field which is the Person_ID, a number. I know I've done this sort of thing before but I don't do database work very often. I'm even having difficulty here figuring out the primary key in the "TRAINING_PARTICIPANTS" table.

Does this make sense? I'd appreciate any help. If any clarification is needed and I don't respond right away it's because I'm going to be without internet access for about a day. Thanks.

 

[PHV]

The PrimaryKey of TRAINING_PARTICIPANTS table may be a composite one: the combination of Participant_ID and Training_ID.
Participant_ID is a ForeignKey referencing PERSON_TABLE(Person_ID)
Training_ID is a ForeignKey referencing VOLUNTEER_TRAINING(Training_ID)

Using JOINs clause in a query you may retrieve the full name of the participants and the full topic of the training.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[amy3000]

Perfect. I knew it wasn't complicated. I just couldn't get it. Thanks.

 

[amy3000]

There's another bit of complication now. I have 3 tables to record trainings and the participants.

PERSON_TABLE
Person_ID (primary key)
Last_Name
First_Name
Etc,

PARTICIPANT_TABLE
Person_ID (primary key)
Training_ID (primary key)

VOLUNTEER_TRAINING
Training_ID (primary key)
Training_Type
Training_Date

I need to add one more piece which is what roles did the trainee participate in. It could be multiple volunteer roles. In looking at it in forms, the PARTICIPANT_TABLE is a subform within the VOLUNTEER_TRAINING form. What I need to do is have another subform within the PARTICIPANT_TABLE which can record multiple training roles. I'm again not sure how to connect the tables.

 

[willir]

Well, is the role and participant transitory or permenent? Since you mention "Trainee", I will assume transitory.

I like calling the "joiner" or "intermediary" tables as profiles.

PARTICIPANT_TABLE
Person_ID - foreign key to PERSON_TABLE
Training_ID - foreign key to VOLUNTEER_TRAINING table
Role_ID - foreign key to role table

The primary key could be Person + Training + Role

This allows you to track the person, the training program and their role (attendee, leader, coordinator, etc)

If some roles are permenent, then you will have to decide where to set the relationship. For example, a coordinator can be stored on the "VOLUNTEER TRAINING" table if they are always a coordinator.

What happens if the training program is offered more than once?
Example. CPR training, and the volunteer takes the training several times, or participates as an assistant. Your current design does not apparently easily allow for this. The "VOLUNTEER TRAINING" table has a date field. You have to either create a new "VOLUNTEER TRAINING" record every time the course or program was offiered, or change the TrainingDate and delete the registered attendees before adding the new attendees.

If this situation does become a concern for you, consider having the VolunteerTraining table to depict the program and a TrainingClass table. The TrainingClass table would track date, location, etc. If you decide to move to this tweak with your design, then I am sure you realize you will have to "register" the participant in a TrainingClass.

Hint: Read the following FAQ...
Avoid space characters in any 'Name' - Why ?

By the way. I think you did a great job in your initial design.

Richard

 

[amy3000]

I'm afraid I'm being a bit dense and I might be asking the same questions over again. I created a composite primary key in the:

PARTICIPANT_TABLE
Training_ID
Person_ID
Role_ID

There's a one-to-many relationship between the TRAINING_TABLE (one part) and the PARTICIPANT_TABLE (many part). But the one-to-many relationship between the PARTICIPANT_TABLE (many part) and the VOLUNTEER_ROLE_TABLE (one part) is backwards. It needs to be one participant and potentially many roles. I can't figure out how to set it up correctly.

A new training will be entered for each new date of training.

I'm getting confused partly because this database is getting too big for it's britches. I needed to match the tables and fields as closely as I could with a State database so that our data could be exported into their's if the time ever came that the State database was adequate for retrieving data (they're a little slow sometimes). As I set it up I could see why the state had done what they did but it's bigger than 30 tables now. These training tables are in addition to the ones the state had. I didn't really realize that it was a bit above my head until I was really into it. I've learned a ton however. I'm getting tempted to do bad design and put a "primary volunteer role" field and a "secondary volunteer role" field into the TRAINING_TABLE.

Thanks for the compliment. I've had a lot of help from this forum and a friend of mine helped taught me a lot about sound database design. But, as I said, I did get in over my head on this one.

 

[willir]

I needed to match the tables and fields as closely as I could with a State database

Well, this is a definite guideline. However, you should be able to achieve the desired report / export format using a SELECT clause that links the various tables.

As per the confusion, I may be confused in understanding your needs. However, the way I test a database is to put in dummy data and see how it looks, and how easy it is to retrieve the information from the database.

[tt]
"PERSON TABLE"
Person_ID Last_Name First_Name
1 Brooks Terry
2 Jordan Robert
3 LeGuin Ursula
4 Asimov Isaac
5 Sawyer Robert
6 Card Orson
7 Norton Andrea
8 Salvatore Robert
9 Clark Arthur
10 Simmons Dan
11 Pullman Philip
12 Lustbader Eric

VOLUNTEER_TRAINING
Training_ID Training_Type ProgramName
1 EH&S CPR
2 EH&S WHMIS
3 SelfImprovement Team Leadership
4 Health PreNatal Excercise

tblTrainingClass
TrainingClassID Training_ID Location ClassDate Organizer
1 1 1 01/15/2005 4
2 1 1 01/22/2005 4
3 1 2 01/22/2005 3
4 2 3 01/14/2005 4
5 3 2 01/24/2005 3
[/tt]

Discussion:
CPR course at location 1 (from the Location table) on Saturday, Jan 15 and 22 organized by Asimov, and at location 2 on Jan 22 organized by LeGuin.

WHMIS training at location 3 on Jan 14 by Asimov.
Leadership training at location 2 on Jan 24 by LeGuin.

Note:
I used the Person table for participants and trainers.
Although I am not sure if you need a Training (Program) table and Class table, I used this for the demonstration.

[tt]
tblRole
RoleID RoleName
1 Participant
2 Facilitator
3 Co-Facilitator
4 Assistant
5 MC

PARTICIPANT_TABLE
Person_ID Training_ID TrainingClassID Role_ID
4 1 1 2
4 1 2 2
4 2 4 2
3 1 3 2
3 3 5 2
1 1 1 1
2 1 1 1
5 1 2 1
6 1 2 1
7 1 2 1
4 3 5 3
3 3 5 2
1 3 5 4
2 3 5 1
8 3 5 1
9 3 5 1
10 3 5 1
[/tt]

Discussion: A lot of numbers...
Asimov is the facilitator for CPR training for class 1 (01/15/05) and class 2 (01/22/05). Asimov is the facilitator for WHMIS training on class 4 (01/14/05)

LeGuin is the facilitator for CPR training (class 3, 01/22/05) and Team Leadership (class 5, 01/24/05)

Brooks and Jordan are participants in CPR training, class 1, and Sawyer, Card and Norton are participants for CPR training for class 2.

For the Team Leadership, class 5, facilitated by LeGuin, Asimov is co-facilator, and Brooks is an Assitant. Participants are Jordan, Salvatore, Clark and Simmons.

But the one-to-many relationship between the PARTICIPANT_TABLE (many part) and the VOLUNTEER_ROLE_TABLE (one part) is backwards. It needs to be one participant and potentially many roles.

In the above example, Asimov is a facilatator (CPR) and co-facilator (Leadership). I could have used this design to have made him a participant in program / TrainingID 4, PreNatal Excercise, but I am sure he would not have appreciated this. ;-)

If you mean that a person can have more than role for one class, you can set this up too.

More discussion on PARTICIPANT_TABLE
Person_ID Training_ID TrainingClassID Role_ID

- I included Training_ID even though you could find out the Training_ID via the TrainingClassID. Proper normalization would exclude this field. However, including the field may make it easier for sorting and retrieval of data. I would have to play aorund with this to determine to keep the field in or not.
- There is no formal primary key for this table, YET. You could use a multi-field key PersonID + TrainingID, OR PersonID + TrainingID + TrainingClassID, OR PersonID + TrainingID + TrainingClassID + RoleID OR PersonID + TrainingID + RoleID depending on needs. You can avoid duplicated data by setting a unique index. However, using a primary key beyond two fields is fround upon by some, and using three fields would be fround upon by more.

So you may want to add a simple numeric ID field...
PARTICIPANT_TABLE
PARTICIPANT_ID - primary key
Person_ID
Training_ID
TrainingClassID
Role_ID

I sincerely hope I helped and did not confuse.

Richard

 

[amy3000]

I am amazed at how fabulous people are on this forum. Yes, this is very helpful. I need to look at the various options. I'm not sure why I've had so much difficulty grasping this. Thanks.

 

[SQLSister]

So RIchard, read much science fiction?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[lespaul]

I tease him about his PERSON name choices all the time!!

He really needs to find some new authors!!!



les

 

[willir]

Hi Leslie -- good to hear from you again. And yes, SQLSister, SF is something that bit me before I was a teenager -- never grew up I guess (typical male?? ;-) ).

Orson Scott Card is new to me. I game my one son Ender's Game and Speakers of the Dead -- he laughed so much, I had to find out why. My other son kept turning his reading light on way after bed time for Dirt Eater and Free Walker (Foone), so again, I had to find out why. Kid's stuff I know, but fun. I am currently working on Big Bang (Singh) and Why boys don't talk (Shaffer & Gordon), and recent reads include Oryx and Crake (Atwood) and Veil of 1000 Tears (Van Lustbader). I keep hunting for Robert Jordan's series in a used book store but, he nust be good -- they are rarely resold.

But honestly, I have been so busy as of late that I only seem to get a scarce few minutes a day for personal reading.

Take care all.
Richard

 

[PHV]

Yeah, I guess Richard don't like Ray Bradbury nor A.E. van Vogt ...

 

[SQLSister]

Or Heinlein!

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[willir]

I read all of Heinlein's books years and years ago -- Stranger in a Strage Land -- lots of fun -- I Grok you too .

 

[amy3000]

Well, since you've all helped me so much I think I can chime in on this topic too. I'm not much of a science fiction fan but Orson Scott Card's "Pastwatch" was very good. I've read a couple other books of his.

 

[traingamer]

Try Neil Stephenson's 'Snow Crash' or one of his other books. I love the classics, too, but I've read most of them already. OR try any John Varley. Wait, Zelazny & Brunner & Niven & ....

Sorry. Couldn't help myself.

traingamer

 

[willir]

Never tried Stephenson - thanks, I will try him. Have read Varley, Zelazny, Niven. And you know, as a parent, I find it a real joy to watch my kids enjoy some of the same books I enjoyed when I was young. SF may not be for everybody, but it can be timeless. My one book-a-holic son, laughed so hard and so often with Douglas Adams Hitch Hiker series, I kept checking in on him to see why?

Amy Thanks for the tip on Pastwatch. Did the "data view" help with putting things together? Often, when I am stumped, I will resort to this approach to flesh things out. ...And then use some SQL queries to ensure you can extract the information you need - very important. If the design is worng, extraacting the information may be difficult.

Richard

 

[lespaul]

Richard, If you liked the Ender's stories, try Ender's Shadow!

Neither of my kids have turned into book-a-holics yet, but I have hopes for one of them!

Leslie

 

[willir]

Leslie
It may take time. Hint: Find a book that they would like (and it helps if it received awards of merit which suggests it is well written). And accidentally leave a flash light and the book in their bed. If the batteries are dead the next day, you may have been successful. This is how I hooked my one son. The other one took more work, but a similar approach where he had to go to bed but could read it he wanted.

How old are your daughters?

 

[amy3000]

Willir,
It does help. I haven't completely worked it out yet (very long week) but I've created a different database with limited data and tables to simplify it.Thanks, Amy