Sniff test; Geographic DB

Discussion created by Peterwhitley on May 19, 2016
Latest reply on Jun 1, 2016 by Peterwhitley


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?