8 Replies Latest reply on Jun 1, 2016 5:20 PM by Peterwhitley

    Sniff test; Geographic DB



      I'm a grants administrator for a nonprofit organization. I maintain an antiquated collection of FMP tables that have been frankensteined over the years. None of the tables are related and they are disorganized and illogical. Due to their sorry condition, it's very difficult to extract the kind of data insights that I need. I'm almost proficient enough now in FMP to create a new design for all of these unrelated tables. I've begun this exercise and thought that I'd present my vision to this community of experts before I invested too much time developing concepts that would result in dead-ends and false-starts.



      I will need this database to store a number of related categories of data. I'm planning on using the following tables.

      1. Grants
        1. Grant Applications
        2. Application Review (executive evaluation)
        3. Grant Tracking (e.g., status of project, etc.)
      2. Contacts
        1. Organizations
          1. Programs (e.g., service programs that align with our work in some way)
        2. Individuals
      3. Geographies
        1. Neighborhood
        2. Municipality
        3. Greater Metropolitan Area
        4. County
        5. State
        6. Region
        7. Nation
      4. Facilities
        1. Assets (e.g., the facilities our organization helps fund)



      With this schema I'm predicting that any grant, contact, or facility can be rooted into a particular geography. It is the geography tables that I am most concerned with at the moment. Here's what those relationships look like:


      Screen Shot 2016-05-19 at 4.17.42 PM.png


      A specific geography is assigned to a place in the typology of geographies, (i.e., neighborhood, municipality, county, etc.). This allows me to quickly compare the captured demographic information against its "parent" geography. Here's an example using placeholder data (and incomplete... but enough to get the idea).

      Screen Shot 2016-05-19 at 4.20.34 PM.png

      Once my geographies are imported, each neighborhood cataloged should relate to its parent municipality, and each municipality should relate to

      its parent county, and each county to its parent state, and so on.


      NOTE: I've created two interstitial tables, greater metro area and region, because those particular geographies won't have the comparable data that the others will. Those two tables are characterized by portals listing their child geographies. In other words, a Greater Metropolitan Area record will show its member Municipalities, and a Region record will show its member States. (This will enable me to see all of the related activity and compare demographic characteristics within those particular geographies easily.)


      This geographic schema will be a support system for those grant applications, program service providers, and facilities. It will allow me to see all of the service providers, grants, contacts, and facilities in a particular geography — great or small.


      Am I on a good track here? Am I reinventing the wheel? Am I inventing a cube that I think is a wheel? Insight?



        • 1. Re: Sniff test; Geographic DB

          I think that you are roughly on track in that you are planning ahead and have a bunch of things well thought out.
          That said, I would recommend some immediate course corrections.
          1. I recommend that you use the anchor–buoy method of managing your Relationship Graph (that is a post by itself). I have seen tons of projects succeed with anchor–buoy and too many that became unmanageable otherwise. 

          2. Looking at your outline, I would agree that the Geographies is the greatest concern. If each geographical level was entirely contained in the one above, that would make things simpler.
          It seems that the ones that you mentioned: greater metro area and region might (depending on how you define them) increase the complexity of the system design. For example, if each state is entirely within a region...less complicated. If a state is spit across a region...more complicated (still very doable...it just raises the difficulty factor).
          I recommend creating an entity–relationship diagram (along with some simple outlines of what the system should do) before doing too much coding. 

          anchor–buoy is your friend

          Hope that helps.
          Tony White
          Tel: 646-714-2797 (Google Voice)
          Tel: 718-797-4175

          • 2. Re: Sniff test; Geographic DB

            This is very helpful feedback, Tony. Thank you.


            I've continued to iterate. The relationships are essentially unchanged:


            Screen Shot 2016-05-21 at 8.26.46 PM.png


            Each tier within the geographical typology has its own layout. Here is a neighborhood, for example:


            Screen Shot 2016-05-21 at 8.26.03 PM.png

            And the neighborhood is contained within a municipality:


            Screen Shot 2016-05-21 at 8.29.11 PM.png


            I've changed the conditional formatting a bit so that if the education or economic (median household income, or MHI) values are "worse" than the parent geography — in this case the state — that value is red. This allows the person accessing the data a quick visual code if the geography being viewed is less- or more-educated, or poorer or wealthier, than its surrounding area. (I removed the conditional formatting for race and age characteristics as it could suggest a value judgment on that community's racial profile.)


            So far, so good.


            Tony, you're absolutely right about the challenges introduced by geographical areas that have more than one parent. That's essentially why I've leap-frogged the county for comparing a municipality's relative wealth. Many cities span several counties.


            Greater metro areas will be handled as a casual association. If a municipality belongs to a greater metro area, it will be entered by hand as needed, or if someone simply doesn't have much to do. (There are basically only two of us in the office that will be using this database on a regular basis, but we will be using it every day for several hours at a time.)


            Here are the other tables (so far) that will be informed by the geographical tables eventually. My goal first is to establish the geographies as it strikes me as the most challenging... and since this is my first time working with Filemaker (or designing any kind of relational database), I wanted to knock out the hardest stuff first.


            Screen Shot 2016-05-21 at 8.40.57 PM.png


            I'm intrigued by the Anchor-Buoy schema but didn't find much online when I searched around. Is there another name for that approach to relating databases, or are you aware of a dedicated thread to the topic here? (I'll continue searching all the same.)


            Thanks again for your feedback! It's very encouraging.



            • 3. Re: Sniff test; Geographic DB

              Hi Pete,


              Here are some Anchor-Buoy resources:

              Anchor Buoy Parts 1, 2, and 3 - Jonn Howell

              There was an article titled “Tame the FileMaker Pro Relationship Graph” that ran in the FileMaker Advisor magazine (no longer publishing) October/November 2006 that you might (or might not) be able to locate.

              Here are our (Tony White Designs, Inc.) Anchor-Buoy AB rules (that we rarely break)

              • Have one Table Occurrence Group (TOG) for each Table
              • Make the first Table Occurrence (TO or to) created for a table the Anchor Table Occurrence (sometimes called the Primary Table Occurrence)
              • Name the Anchor Table Occurrence the same as the Table it is based on
              • All layouts are based on an Anchor Table Occurrence
              • We use/advise natural table names (see below) as components of the Table Occurrence name. (others advise using codes for tables)
              • Use some lower case text to note (to_note) the nature of the relationship between 2 Table Occurrence, for example [id]
              • use 3 [_] chars to delimitate Table Occurrences (+ to_notes)
              • use 2[_] chars to delimitate to_note

              Example: Imagine a 2 table system with:

              • Customer
              • Invoice

              In a non Anchor-Buoy system you would have one TOG with 2 Table Occurrences
                    1. Customer -< Invoice

              In an Anchor-Buoy system you would have 2 TOGs with 4 Table Occurrences (2 Table Occurrences each)
                    1. Customer -< Customer___Invoice__id
                   2. Invoice -< Invoice___Customer__id

              Hope that helps.
              Tony White

              • 4. Re: Sniff test; Geographic DB

                Terrific. It's easy to immediately recognize this as a superior design philosophy because it is essentially layout-centric. This will allow me to focus on the user flow of each layout uniquely rather than trying to comprehend a single map that anticipates every potential desire.


                Naming table instances is going to be super important. Relying on some obscure naming convention is going to lead to a massive headache once there are several occurrences of each table all serving different anchor tables.


                I'm testing with data for the next few days to worry out anything I may have overlooked. When I start in on the non-geographical tables I will begin with this architectural model.


                EDIT: I found the excellent Kevin Frank overview over the weekend, but your explanation was much clearer to me.

                • 5. Re: Sniff test; Geographic DB

                  Thanks. I am glad that you found my post helpful...it is easy to make things clear when you are standing on the shoulders of giants who blazed the trail years ago.

                  When FileMaker 7 came out I was knee deep in 3 giant FileMaker 6 projects. The core of Anchor-Buoy design had been mostly worked out when I started building systems in FileMaker 7 and up...

                  These days we have many 100+ table Anchor-Buoy based systems and they all scale in scope while not scaling in complexity.

                  If it was me, I would rebuild with a pure Anchor-Buoy design before deploying to users. After you deploy to users you have fewer options.

                  Some pain now to avoid more pain later.

                  Anchor–Buoy is your friend for the long term

                  Hope that helps.

                  Tony White
                  Tony White Designs, Inc.
                  Tel: 646-714-2797 (Google Voice)
                  Tel: 718-797-4175

                  2 of 2 people found this helpful
                  • 6. Re: Sniff test; Geographic DB

                    Had a basic question about terminology that I am hoping you might have input on. One of the problems that I'm regularly facing while trying to learn the mechanisms of FMP design is that I'm unfamiliar with the terminology. So I'm looking for the proper way to ask this question:


                    My ideal scenario is that I am adding a new person to my Contacts table. I put in their name, street address, phone and email... all of which are unique to that person's name, so they exist in the same table. When I add a person to the Contacts table, I want to have their record reveal information about their city, such as population.


                    The city, however, might be shared by other individuals in my Contacts table. For the City field I want to refer to a related table.


                    What I'd like to see happen is this. When I begin to type the city name, a pop-up list of cities that match those characters appears.


                    • If my city is in that list, I can select it and have that city's population automatically appear in the "population" field.


                    • If my city is NOT in that list, I can select "Add New" or simply write it in (along with its population) and have that city then be available to future new contacts that may be from there.


                    This strikes me as a very common and traditional behavior but, unfortunately, I don't know what the operation is called. (I'm having a hard time learning how to do it as a result.) Can you share with me the terms that I seem to be missing?

                    • 7. Re: Sniff test; Geographic DB

                      You can do what you describe using what is generally called a “pick list”.

                      In your case we would most likely put addresses in a separate table and link other entities, for example, Contacts and Projects to the Address table. Relational structures for the win. Please note that addresses are not unique to people. Husbands and wives usually share the same address and often have separate contact records.

                      We use pick lists that have “New” buttons so that keystrokes are recycled if the address is found to be New.

                      We would also have a separate table for City to store the demographic info for that entity and relate to Contacts or Projects, etc. as needed.

                      Pick List+New is a very useful pattern in FileMaker systems where you want to minimize typing and avoid duplicate records.

                      Hope that helps.

                      Tony White

                      1 of 1 people found this helpful
                      • 8. Re: Sniff test; Geographic DB

                        Yes, this is perfect. Sometimes the biggest challenge of learning something is in the jargon. Thank you for the advice.


                        I've begun to remodel the relationships in my database according to (what I believe to be) your recommendation. Here's the basic gist of it.


                        Screen Shot 2016-06-01 at 5.11.06 PM.png


                        The geographies (blue) are nested, like matryoshka dolls, so that the more exacting geographies should relate to their parent geographies accurately. Most of the data entry will be done as a function of other work duties and so the need for data entry should become lighter over time (as more geographies are populated).


                        The agents (purple) are the companies, organizations and individuals we interact with. They are generally rooted in a specific geography and I'm currently linking them to Cities. I may change that as some of our partner organizations are active in larger areas, or several areas. This strikes me as a small problem to address.


                        Applications is the table for grant apps. These are user-submitted and remain essentially unaltered by staff. There is a Tracking table for managing grant awards. I'll be creating a table related to Applications for staff review and evaluation of those applications. (That's not shown in this map.)


                        Finally, the Park Index catalogs facilities in the built environment. These are permanent, fixed places that will later be related to points in the Geographic tables. Some of those facilities will be ones that have an accompanying Application, hence the relationship there.


                        I haven't had much time to test this arrangement but I think it's reasonable. So, thank you!