3 Replies Latest reply on Feb 6, 2015 2:32 PM by philmodjunk

    Dealing with overlapping criteria when organizing people

    DuganMorgridge

      Title

      Dealing with overlapping criteria when organizing people

      Post

      I am part of a group of people working to start a non-profit organization, and since I have experience with Filemaker already, I've been tasked with designing and implementing a database to help us track all our people and relationships.

      The organization is a chorus, which will be made up of both professional and volunteer singers.  Additionally, we need to be able to track individual donors, institutions that donate or who are prospective donors (corporations/foundations/businesses), and possibly also peer organizations.

      The basic problem I'm facing is avoiding redundancy.  There will be a great deal of overlap between the pool of singers and the pool of individual donors.  This is further complicated by the fact that some of the singers are married or cohabitating couples, so for the purposes of donation acknowledgement or mailings, they are a single entity, but as employees/volunteers, each individual needs to be tracked separately.

      I'd like to avoid keeping separate records of contact info (address, phone, etc.) for the same people, but I also don't want to have a single "People" table that contains a bunch of relevant fields for Singer information that only gets filled in if they happen to be singers. 

      My head is spinning trying to figure out how many tables to use and how to structure their hierarchy.

        • 1. Re: Dealing with overlapping criteria when organizing people
          philmodjunk

          but I also don't want to have a single "People" table that contains a bunch of relevant fields for Singer information that only gets filled in if they happen to be singers.

          That really isn't such a terrible thing to have in your database. These fields can be put on a separate layout or inside the panel of a tab control and only become visible/accessible when the current record is for a singer.

          But the singer specific records can also be fields in a related table where you only create that record in the related table when the current record is for a singer and not a non-singing donor.

          • 2. Re: Dealing with overlapping criteria when organizing people
            DuganMorgridge

            Thanks for the input.  I'm currently using FMPro 12 (and upgrading isn't an option at the moment) so I don't have the option of having layout objects be selectively visible.  But I hadn't thought much about using different layouts that way. 

            Here's a scheme I did come up with - please let me know if you think this is getting "too cute" -

            One table, Constituents. Will contain physical address, mailing address, one primary phone number, probably a few other fields that are less functionally essential.  Most importantly will have a type field to contain one of several possible values: Household, for individuals/single family units, Foundation, Peer Organization, School, etc.

            Another table, People.  Each "person" record to contain phone numbers, email address, etc. specific to one individual. Each record in People to be a child record to a record in Constituents. Each Constituent should have at least one related record in People.

            A third table, Choristers, to contain information that would only be kept for singers - voice part, tax ID's for pro singers, etc. These would be child records of People, to be created only for people who are singing members of the group.

            There would then also be a Gifts table to track donations, which would be child records of Constituents.

            And also some other tables relating to chorus activities, but which don't really have an effect on the organizational problem I'm grappling with.

            • 3. Re: Dealing with overlapping criteria when organizing people
              philmodjunk

              so I don't have the option of having layout objects be selectively visible.  But I hadn't thought much about using different layouts that way. 

              But you do. Making layout objects appear and disappear is something I've been doing for many years and many versions. FileMaker 13 just made it much, much easier.

              But all I really meant was that you could choose to put the "singer" fields on the panel of a tab control where you click a tab to fill in those details when you need to and just leave it alone when the record is for a non-singer.