Data Model - Current Version

The data model employed by the database is designed to meet needs encountered repeatedly during our work with a wide variety of client organizations. We mindfully selected data handling approaches that avoid problems seen frequently in non-profit databases.

Data Subjects Key Features Experience In Use Diagrams Earlier Versions

Data Subjects

The following major data subjects are covered:

Action Teams - Membership of parties in action teams. Action teams may be designated as being associated with outreach events.
Contacts - A contact represents a communication between a staff member and another party. Examples would include email messagess, phone conversations, letters, face-to-face meetings.
- Participants - parties participating in the communication, and the role they played
Grants
- Deliverables
- Milestones
Organization focus areas - overarching program areas that might be associated with volunteer actions, outreach efforts, etc.
Outreaches - Bulk communications such as tabling events, bulk mailings, emails, press conferences, workshops, surveys, etc.)
- Associated donations
- Expenses - Expenses associated with the outreach
- Related organization focus areas
- Related grant deliverables
- Responses - Count of each type of response. Might include: letters to the editor, phone calls, new members, petition signers, radio articles, etc.
- Staff Roles - Staff members associated with the outreach and the role they played.
- Targeted parties - Records documenting which parties were targeted by a specified outreach may be automatically created during export of records for mail merge, etc.
Parties - A party record can represent an individual, business, organization, foundation, family, etc.
- Categorizations - categories to which the party belongs
- Email addresses
- Group memberships - A membership in a group that includes a stated start date and end date. This might include formal memberships, subscriptions, committe memberships, etc.
- Locations - Physical locations with an address.
- Party members - Persons who belong to the party
- Phone numbers
- Pledges - Promises to make future payments. Links to expected future payments can be tracked.
- Volunteer actions - Tracking of each instance in which a party undertakes an action as a volunteer.
Payments - A monetary contribution, in-kind contribution, or expected future contribution, grant funding, or merchandise payment
Payment Products - The ability to apply each payment towards multiple producst.
Persons - Each person record represents a single individual. This is distinguished from a party, which might represent a business with several persons associated, etc.
- Contact preferences - Tracks willingness of party to be contacted by phone, email, letter for purposes of alerts, renewals, solicitations
- Ethnicity - The ability to track an arbitrary number of ethnic associations for a specific person.
- Languages - The ability to track an arbitrary number of language capacities for a specific person has been added.
- Personal events - Important personal events like birth of a new child, business acquisition, death of spouse, etc.
- Racial Background - Racial identification. An arbitrary number of codes may be entered for persons identifying several categories to be appropriate.
- Related Persons - Tracking of relationships to other persons, together with a code representing the type of relationship. Relationship types might include friend, spouse, business partner, sibling, employee, etc.
- Voting information - Information such as that generated by the League of Conservation Voter's voter enhancement process may be imported and stored.
Products - The ability to track products that might be purchased using a payment. Products associated with a payment may include: contributions, ticket/merchandise purchases, memberships.
- Product Memberships - Each entry into a price set may indicate associated group memberships.
  For instance, a price set listing for a standard membership might indicate automatic enrollment in:
  - standard members group
  - newsletter subscribers group
  - annual report recipients group
Product Categories - Categories assigned to products.
Product Price Sets - A set of product prices to be applied simultaneously and for a finite time period. For instance, pricing history for different years might be tracked using separate price sets.
Publications - Publications of the organization. Brief summaries of individual volumes/issues may also be stored.
Staff - Records representing staff members, for use in selection lists on data entry forms, etc.
Selection Criteria - Record selection criteria entered on a record selection form, stored for later recall/use on the original record selection form form.

top of page

Key features:

There is no limit to the number of persons who may be associated with a single party such as a business, foundation, family, etc.
There is no limit to the number of locations that may be associated with a single party
There is no limit to the number of phone numbers that may be associated with a single location
Phone numbers can be associated directly with a party (as for a mobile phone) or directly to a location (as for non-mobile phones)
There is no limit to the number of email addresses that may be associated with a single party
There is no limit to the number of different parties a single person may be associated with. For instance a single person may be readily linked simultaneously to a family, business, and individual records
Group membership records (such as membership in a committee, a group of newsletter subscribers, staff members, etc) are stored in a time-dated fashion. This allows storage and recovery of membership lists from arbitrary times. For instance, the list of persons who were on the board on 1/1/2003 is readily recoverable.
Expected pledge payments may be entered in advance to allow ready retrieval of expected future pledge payments, and to provide reminders of pledge payments that are due.
Donation records clearly distinguish promised payment amounts from delivered payment amounts. This prevents errors in reports that summarize payments already recieved
An arbitrary number of direct staff communications with a party may be tracked. These may be recovered by searches against communication subject, participating staff member, the participaing non-staff party, keywords in notes field, etc.
There is no limit to the number of ethnic associations entered for a single person
There is no limit to the number of racial categories entered for a single person
There is no limit to the number of languages that can be associated with a single person

top of page

Experiences in Use

In actual use, we have found the data model to be quite comprehensive. Client requests for a new data storage or reporting capacity are generally accomodated without revisions of the existing data model. Typically, the most difficult work required in response is the construction of appropriate SQL statements for withdrawing data for a new report from already-existing data structures.

top of page

Entity-Relationship Diagrams

Main Model: Entities and relationships without attributes represented (PNG file, 83Kb)
Contact Submodel: Entities closely related to storage of contacts, with attributes represented (PNG file, 22Kb)
Focus Area Submodel: Entities closely related to storage of focus areas, with attributes represented (PNG file, 14Kb)
Grant Submodel: Entities closely related to storage of grants, with attributes represented (PNG file, 22Kb)
Load Submodel: Entities closely related to storage of loading specifications within the server portion of the system, with attributes represented (PNG file, 13Kb)
Outreach Response Submodel: Entities closely related to storage of outreach responses, with attributes represented (PNG file, 16Kb)
Outreach Submodel: Entities closely related to storage of outreaches, with attributes represented (PNG file, 26Kb)
Party Submodel: Entities closely related to storage of parties, with attributes represented (PNG file, 35Kb)
Payment Submodel: Entities closely related to storage of payments, with attributes represented (PNG file, 50Kb)
Person Submodel: Entities closely related to storage of persons , with attributes represented (PNG file, 38Kb)
Publication Submodel: Entities closely related to storage of publications, with attributes represented (PNG file, 9Kb)
Team Submodel: Entities closely related to storage of teams, with attributes represented (PNG file, 18Kb)
Volunteer Action Submodel: Entities closely related to storage of volunteer actions, with attributes represented (PNG file, 18Kb)
Voter Submodel: Entities closely related to storage of voters, with attributes represented (PNG file, 15Kb)

top of page

Data Models - Earlier Versions

Data Model - Version 3: Page describing differences between V3 and V4
Data Model - Version 2: Page describing differences between V2 and V3
Data Model - Version 1: Page describing differences between V1 and V2

top of page

Last modified: 12:47:29 27-Feb-2018