In designing our non-profit CRM, we have been looking at all of the functions that we’d like to track.  At a minimum, we think each of the entities above will use one database table. In practice each entity will have an associated “interaction” table and “look-up” tables that feed picklists of options.

A little background. Our organization makes grants to schools and community groups. Many of these grants are funded through commercial sponsors, or through philanthropic individuals or organizations. Thus we have both Grant Prospecting and Grant Application Management functions.

Grant Prospecting — This is us prospecting and managing the workflow involved in applying for a grant. Recently when it gets  down to the application process itself  we have we have worked in Slack.

Grant Application Management — As we receive grant applications we have to process them and eventually evaluate them. We currently use LimeSurvey for our online grant application.

Sponsor Prospecting — If we don’t have an individual or foundation donor, we may fund a program through a sponsor; a company that wants to promote their community engagement or charitable contributions.

Contact Management — The contact table part of our design includes all the usual information about contacting a person In our database design, this may actually turn out to be  a hidden many to many table. One of the objectives that I want to address is the ability to look at a single person’s contact information and determine what our relationship(s) are with that person. A person could be a donor, a member, a volunteer, a member of our board, member of our advisory board. and our lawyer. This is really what CRM is all about, as well as the ability to track the relationship steps with the person at any stage of the relationship.

Membership (Donor) Management. We used to do this in Little Green Light. We’re not sure our business model going forward lends itself to membership.. but if it does, this is where we’d like to track members.

Media / Outreach Lists — We have 3782 media contacts to which we send press releases etc.

Admin — Could we provide forms for our usual HR functions like time sheets, vacation accrual, expense reimbursement, travel schedules, etc?

Note that this design exercise is independent of any particular kind of software. We have bits of data scattered in spreadsheets, word-processing files, online applications such as LimeSurvey and Little Green Light, FileMaker databases and of course eMail.

Update: I’ve added two additional tables; one for “contacts” (which will feed its contact and address data to the other tables), and  “projects” table which contains data about each project.