8 Replies Latest reply on Jan 6, 2015 4:34 PM by mikeg998

    Relationship design and layout subets


      I'm completely new to Filemaker and relational database design but have none the less been asked by an non-profit to help set up a system for them. Would love your opinions on whether I'm on the right path.


      The general work they do is with refugee populations. They are wanting to track contacts, donations courses and events. Contacts can include family members (donors), volunteers, refugees, and employees.


      Question 1)       I'm thinking that all the contacts and information about the individuals should go in 1 table and then I would create layouts that shows subsets (using a script). i.e. If they volunteer than they would show up in a layout for volunteers, same if they are refugees, etc. 

      Is this a smart way to go about it?  Many people might be fit into several categories and thus would need to show up on multiple layouts. I want there to be one layout for each department with their subset/found set already loaded.


      Question 2) Would I need to create an instance of the main contacts table for each of the layouts or just write a script that subsets the main table for each layout?


      I pull family members together by linking contacts with a families table containing a pk and home address   [contacts]>-------[families (address)]


      For Courses I have as:   [contacts] -------< [Contact/Enrolment (join)] >----- [courseList] >--------[Instructor (instance of Contacts table]

                Q: Again, do I create an instance of the contacts table, or create another link to the original contacts table?


      Similar idea for the events table.

      Donations:     [contacts] ------< [contacts/Donations (join)] >-------- [donations] >--------[Event (if its tied to one)]


      Am I on the right track here or do I need to rethink he whole thing?

      Sorry for posting what I'm sure is a very newbie question. Thanks in advance for your comments/suggestions.

        • 1. Re: Relationship design and layout subets

          Hello Mike,


          First things first. Some of your questions about when to create new table occurrences and when not to—there's rarely a single best answer, but there are some recommended approaches—may be addressed in Ray Cologon's excellent "Approaches to Graph Modeling: 9 Steps Toward Enlightenment With FileMaker Pro" white paper linked here.


          I know you've asked more questions than that, but wanted to get that in your hands first.





          • 2. Re: Relationship design and layout subets

            That out of the way, why do you want different layouts for different subsets of your Contact table, if I may ask? Is it because you need to show different fields for, say, a volunteer vs a refugee? If not, then you can usually use one layout and filter it to show different contacts, by role, on the fly. In this case, you'd obviously only need one TO for that layout, but even if you do stick with needing different layouts for different contact roles, you still would, in most cases, base those layouts on the same instance of the Contact table, providing a single context for the relationships needed to support those layouts.






            P.S. No apologies needed for the "newbie question" (your words). Your relationships (while probably due for some tweaking along the way) are already showing a bit more sophisticated understanding of relational design than some of us had when we started out.

            • 3. Re: Relationship design and layout subets

              Thanks! Will dig into this right away.

              • 4. Re: Relationship design and layout subets

                There are a couple of staff members that deal only with refugees, a few that deal only with volunteers, and so on. I though I'd make it easy for them and build a home/startup screen that had a button taking them to the layout with fields dealing only with the data they needed.


                So, with only one instance of the contacts table, I would just need to link all the supporting tables (donations, staff, courses, families, etc.) to that one table?


                Thanks for your quick and courteous reply. My background is in research design and analysis so I'm feeling a bit overwhelmed by database design. Want to keep it as simple as possible but make sure I'm building it the right way.

                • 5. Re: Relationship design and layout subets

                  One thing I don't hear clearly expressed yet is the idea of join tables.

                  Yes; a single table for all people no matter what their place in the organization.

                  But you don't put all the fields in that table.

                  You use other tables, linked by ID, to handle various classes of information.

                  So for instance you might have a staff table; and a refugee table.

                  The refugee table would have contactID, countryFrom, date, etc.

                  The staff table would have contactID; staff position; start date; job title; etc.

                  You might have an Assigned table which has a staffID, date, and refugeeID.

                  Both staffID and refugeeID "point" to their respective records in the People table.

                  • 6. Re: Relationship design and layout subets

                    Hi Mike

                    Following Mark's lead, I am going to point you to some examples that you can learn from or perhaps use as a starting point and then morph them into your own. It will be much quicker to get a system up and running as well as knowing that it was designed by pros and thus the system has a solid foundation from which to build on. This will prevent any instances of "geez, if I only knew then what I know now, I would not have dug myself in such a hole!"


                    Both Apps are from Richard Carlton Consulting and are free. Check them both out, look under the hood, you will be glad you did.

                    FileMaker Donations


                    FM Starting Point - Free FileMaker Template by RCC


                    one is more specific to non-profits but the other may be stronger with some modules that can be repurposed.


                    Not trying to kill your joy of starting from scratch and  learning as you go.


                    However, if nothing else, these apps should be a great source of learning.


                    You may find their relationship graphs a bit intimidating/overwhelming at first,  but again, a great source to learn from and a great starting point should you decide to go that route.


                    Happy FileMaking

                    Rob Bloomfield

                    • 7. Re: Relationship design and layout subets

                      Thanks Bruce,


                      Yeah, that's a great point. I had thought about doing that but was worried about one to one relationships. The joins would solve that.

                      • 8. Re: Relationship design and layout subets

                        Thanks for the advice Rob. The geek in me wants to build it myself but I may not have the time. I can at least start with breaking theirs apart to see what does what. I'll have to stare at the Starting Point relationship graph for a while to see if it sinks in. Definitely intimidating.