getting confused with relationships (primary key, foreign key, fields from related tables, etc.)
I realize there is much redundancy in what is asked and what is answered on this forum, but so often it's hard to grasp these concepts to the extent where they can be applied to very specific scenarios. I feel like I'm getting there, and I thought I had experienced that "a-ha" moment where the lightbulb went on over my head, but I've found that I must still be missing some portion of the fundamental understanding behind how related tables work.
I'll try to quickly summarize where I'm at:
- I have a church membership stored in a table called member_records
- I have a list of committees in a table called committees
- I used a join table called committee_assignments to connect the member_id to the committee_id
- I can now populate a committee by adding names to a committe record via a portal on a committee record layout
- I can also populate a member's list of committee assignments by going to their member record and adding committees via a portal
- committee assignments using either method result in records being added/deleted properly on the join table
- I can choose a chair for each committee by selecting from the members assigned to the respective committee
Here's where I'm running into trouble. I now want to create a table for our church Executive Board which is made up of six elected members. I do not want to treat this as a committee like the other committees. I want this to be separate. So I created a separate table called executive_board, and I'm wondering what fields I need to have that are unique, what fields should simply be calculated from existing data elsewhere, and what fields should be related to fields in other tables. Also, I had assumed that I would use the member_id as the field with which to relate the two tables, but now I'm not so sure. So far I had created a field called executive_board::position, and I created a check box set of "Female 1," Female 2," "Female 3," "Male 1," "Male 2," and "Male 3." When I had just the very basics, I could relate executive_board to member_records via this position field, and then I could go to a member's record and check one of these boxes to "assign" them to the board (they would show up on my executive_board table). However, things went haywire when I then tried to go to a committee record and assign a "board contact" (each committee has an assigned board member to act as a liaison). I'm not sure what to do. Can the existing thread of relationships work "as is" where member_records is a sort of hub and I can see a chain that runs all the way from executive_board through member_records through committee_assignments to committees? Or do I need to establish new relationships? A direct relationship between executive_board and committees, perhaps? Exactly where and why and using which fields? To get the check box functionality, I created the relationship between executive_board and member_records using the "board_postion" field, but should I be utiilzing the member_id (I thought I remember reading somewhere that this serial number set-up is really important for relating tables). Now that I have this long chain of relationships, what's the best approach when placing a field on a layout? Using a portal and looking at the field from another table, or using a calculation, or using a relationship? I'm so confused. I don't think I'm beyond help, but I need someone to be patient with me and help me understand the principle behind how this works. I feel like I'm close, but obviously I'm not quite there.