Event DB I am working on.

[ccaste]

OK. So I am very new to FM. I have been using Excel for many years doing DB-like things. But I figured it was time to move on and start using an actual DB for my projects. I am confused and I have been looking for something that makes sense online to answer my question. But I am still stuck. Here is my problem and I hope someone can help me with a solution.

I am building a DB that will track events that my church holds every three months. We call them Encounters - kinda like a retreat. Anyway, we have participants that attend and volunteers that, well, volunteer. A person can be a participant only one time but once they participate, many want to volunteer to serve in these Ecounters. And a person can volunteer many times. In addition, many of the volunteers are assigned participants. So each volunteer can be assigned multiple participants. And finally, both participants and volunteers must pay to attend these events. I would like to keep track of these payments. So a payment can be received for a volunteer or participant. For volunteers, I must be able to assign a payment to a specific volunteer and specific event.

So I am trying to set up my relationships. I have setup each Event with an eventID that is auto generated and unique. The event information is located in a table I have titled tblEncounters. It contains all the event details - location, dates and so on. I know that I should have a one to many relationship to the participants. But I want to also have a many to many relationship to the volunteers. I am blurry on how to set that up. I know it has to do with a seperate table that joins these to, but... The other thing is that the volunteers and the participants need to have a relationship as well - one volunteer to many participants in a specific event.

I hope this makes sense to someone who can throw me me bone.

[ccaste]

So I have been thinking this out a little more. A participant has a relationship to both an event and a volunteer. An event has multiple volunteers that are assigned to many participants for that event. A participant can only be assigned to one volunteer at a specific event. I don't know if that adds anything to what I posted before, but...

[comment]

How many volunteers can a participant have?

[ccaste]

A participant can have only one volunteer.

[comment]

I believe something like this could work:

Notes:

1. This is an ERD, not a relationships graph - some tables will need more than occurrence on the graph when implementing this in Filemaker.

2. The Attendance table needs a Role (Participant/Volunteer) field.

3. You will need some kind of validation to ensure a person has only one "Participant" record in the Attendance table.

4. The Payments table is required only if there can be multiple payments per attendance - other wise you could enter the payment directly into the Attendance table.

[ccaste]

I haven't quite figured out how to post an image to the message board. So I can't post the picture of my relationships graph. Basically, I created a table for each item that you put in a box:

tblPeople

tblAttendance

tblEvents

tblPayments

Then I created each of the ID's in the table that represents the One (this meant no PaymentID - is that correct?):

VolunteerID in tblPeople

PersonID in tblPeople

EventID in tblEvents

AttendanceID in tblAttendance

I then created a corresponding IDfk in each of the tables that represent the Many:

VolunteerIDfk in tblAttendance

PeopleIDfk in tblAttendance

EventsIDfk in tblAttendance

AttendanceIDfk in tblPayments

Is this basic structure correct? I realize that I still have to work on some of the other details. I biggest question is about the VolunteerID and PersonID. How are they different?

Thanks for all your help!!

Carlos

[comment]

Is this basic structure correct?

Yes, except good practice requires an ID in every table - even if that table does not have a child table (yet).

I biggest question is about the VolunteerID and PersonID. How are they different?

In the Attendance table, the PersonID contains the ID of the person attending. This could be either a participant or a volunteer. The VolunteerID contains the ID of the volunteer assigned to the participant - and if the attendant is not a participant, this field will be empty.

Obviously, at least one of the tables (People, Attendance) must have a duplicate occurrence on the graph, since Filemaker will not allow more than one relationship between the same TO's. A lot here depends on how you plan to enter the data, i.e. from what point-of-view.

[ccaste]

Yes, except good practice requires an ID in every table - even if that table does not have a child table (yet).

In the Attendance table, the PersonID contains the ID of the person attending. This could be either a participant or a volunteer. The VolunteerID contains the ID of the volunteer assigned to the participant - and if the attendant is not a participant, this field will be empty.

Obviously, at least one of the tables (People, Attendance) must have a duplicate occurrence on the graph, since Filemaker will not allow more than one relationship between the same TO's. A lot here depends on how you plan to enter the data, i.e. from what point-of-view.

I get it. So every person has a personID that identifies them. But only the participants will have the volunteerID filled in for them. This represents the person who is assigned to them. Volunteers don't have anyone assigned to them, so thier volunteerID is blank.

I had set up each of the ID's as: Indexed, Auto-enter Serial, Unique, Allow Override. Should I make any changes to those? I am thinking that a person should not be given an Auto-enter Serial for the the volunteerID because it will actually be the peopleID of the volunteer that is assigned to them. Is that correct?

Also, I have a question as to why the Role field is in the Attendance table and not in the People table. Does it even matter?

I was going to create a "Registration Form" that would be used to enter all the information for each person attending (participants and volunteers). It would contain all the person's basic contact information (name, address, phones, DOB, etc.). In addition, it would have a spot to choose if this person is attending as a volunteer or a participant. But I would like to use it to also assign a volunteer to each of the participants and to view what payments have been applied to their registration to that event. Finally, I just realized that not all volunteers have people assigned to them, some volunteer to do other things. So we actually have participants, guides (this is what we call the volunteers who have people assigned to them) and staff (all the other people who volunteer but don't have people assigned to them). I hope this doesn't complicate things too much.

Again, thanks for all your help! It is greatly appreciated.

Carlos

[ccaste]

There is a basic concept I seem to be missing, because it doesn't seem to be working for me. This is my relationship graph.

[ccaste]

OK. I have an update. I seem to be getting a better handle on these concepts. I am not worrying too much right now about the relationship between a volunteer and a participant. What I want is to be able to get the relationships right among the people and events. I think I have that down. I created a layout that is titled Registrations. This is what creates a unique record that combines a person at a event with a specific role (and if they are a volunteer they will have a group of participants that they are associated with as well). I understand now why you put the Role field in the Registration table (I changed the name from Attendance). I can't link it directly to the person because he person could com back and have different roles in subsequent events. I see why you also recommended to link the payments to the Registration. It's all making sense now and it is actually working in FM.

[comment]

I had set up each of the ID's as: Indexed, Auto-enter Serial, Unique, Allow Override.

It's not necessary to validate serial IDs as unique - it only slows the system down. However, you should turn on the 'Prohibit modification…' option.

The VolunteerID field in the People table makes no sense - a person is identified by their unique PersonID, no matter the role.

As long as you are leaving aside the linking of guides to participants, your relationships graph can be simply:

People -< Registrations >- Events

                  |

                  ^

              Payments

Only when you implement the second relationship:

People::PersonID = Registrations::GuideID

you will need a second occurrence of one of these tables.

[ccaste]

Only when you implement the second relationship:

People::PersonID = Registrations::GuideID

you will need a second occurrence of one of these tables.

OK, this is what I am working on now. Everything else looks good, but I am having trouble getting this relationship set up correctly. Here is a picture of my relationship graph. As you can see I have a second occurrence of the People table with a link to the Registration table - linking the PersonID to the GuideIDfk (I think it's still listed as VolunteerIDfk). Is that correct of should I just have the VolunteerID listed on the Registrations table only, no need for the VolunteerIDfk somewhere else.

Once that relationship is set up correctly, how do I make the link. I am still blurry on that.

Thanks again!

Carlos

[comment]

I have a second occurrence of the People table with a link to the Registration table - linking the PersonID to the GuideIDfk (I think it's still listed as VolunteerIDfk).

Technically, it is a correct solution - one of several possible. As I said, a lot depends on how you plan to work with this data.

For example, your solution allows placing a portal on the layout of People showing the person's own registrations - but not a portal of the person's guidances on other people's registrations. These will be available only from a layout of People 2.

[ccaste]

Ok. So I have each person in the database - guides and participants. I also have registrations that add people to an event. How do I link the guide to the participants that they are assigned to? I created a People Form that is linked to tblPeople - I put all the basic contact information for anyone that wants to register for an event. I also have a roll field in the registration that tells me if the person is registering as a participant or as a guide. How do I tell FM that a particular guide is assigned to a participants?

I have another form that I titled Registration Form that is linked to tblRegistation. I am basically using it to register a person to an event. Though I have also created a portal in a tab in the Peple Form that allows me to register a person to an event. That is working well. Do I need another form to create the link to the guide or can I do it right in the registration form?

I would like to create another tab in the People Form and add a portal to it that shows all the participants that a guide has been assigned to. I would also like to do something similar in the Registration Form that shows all the participants that have been assigned to this Guide for this event.

If I need to change my relationships please let me know. I am getting really confused with this one.

Thanks again!

Carlos

[comment]

Here's a simple sketch that will hopefully get you going. Note that this by no means finished: there's still a lot of work to do, esp. on user interface and data integrity.

EventGuides.zip

[ccaste]

Wow, I think this will help tremendously. I think I was was creating the link, but I did not have a way to get the optional Guidances section. I was not able to show the occurances of a person being a guide for another person. I noticed that there is no GuideID in the Contacts table that is a primary key. Is that not needed?

I hope that I am not frustrating you. I really do appreciate your help. I have been looking all over the internet for about a week to try and find answers, but I guess it's tough to find someone else who has created something just like what I am trying to create. In addition, I don't always know how to word my question so that I can find an answer.

You have been great!

I also hope that you continue answering as I am sure that I am not done asking.

God bless.

Carlos

[comment]

I noticed that there is no GuideID in the Contacts table that is a primary key. Is that not needed?

No - see post #11 above.