How To Set Up a CRM Database for FileMaker

In our previous blog post, we reviewed what a customer relationship management (CRM) system is and put together the requirements for a basic CRM. Yet, we’re not quite ready to start writing code. Next, we need to draw up an entity relationship diagram - ERD for short. In database land, this is a map of all the buckets of information that we will track and the connected data among them. As part of this process, we will look for places to normalize the data. Normalizing means we will look for instances where we can avoid entering the same data in two different areas.  

Set Up CRM Data Tables

Let’s start with our main three modules: Contacts, Companies, and Interactions.

Next, let's think about how they would be linked. A company can have multiple contacts, but generally, contacts only have one company. Interactions are usually associated with an actual person, so we would only link interactions to contacts. What does that look like? In an ERD, we indicate the nature of the relationship using a specific kind of line notation connecting the entities. It might look something like this:

Now one piece of information we haven’t accounted for is the actual contact information – like phone, address, email, etc. What’s unusual here is that both the Companies and Contacts modules will need their own unique place to hold that information. We need to be able to differentiate and easily find the company phone number as opposed to the contact's phone number.

The temptation here would be to include a phone number field in both the Contacts module AND the Company module. But it's less than ideal to store the same data in two places within a database. For one, we duplicate the effort of creating and maintaining the fields. Secondly, validating the data and running reports is easier when we store the data within the same table. And finally, a single table provides an efficient way to collect multiple company and contact phone numbers of various types, including local office lines, toll-free numbers, mobile, etc. So, the ERD looks something like this:

Now we're going to add tables for e-mail and physical addresses, which we treat like the phone table. 

That completes the ERD of our CRM. As stated before, this is a basic ERD. We’ve made some decisions along the way to keep it simple. For example, we could have interactions just with the company at some point. And an argument could be made for putting all the phone and address information into one table. But this is enough for us to start creating things in FileMaker. 

Create CRM App

Let's start by creating a brand new FileMaker file, and we can save it to our local machine. We'll call it “CRM_Example.” Next, we go into Manage Database to start adding the tables. Note that FileMaker automatically creates one default table with the same name as the file we created. We deleted that table, but we could rename and use it. Then we add a table for every entity on our ERD. Without going into each table yet, the list looks like this: 

With that complete, we'll go into each table and add the relevant fields according to how we plan to organize the information. For example, we will add first and last name fields to the Contacts table. Likewise, phone number and phone type belong in the Phone table, and so on. 

We refer back to our initial brainstorming about what we need the CRM to do for this. For a real-life project, we would have multiple conversations with key stakeholders for whom we were building this to determine the required fields. 

We rename the “Primary Key” field to “ID” to keep things as simple as possible. We also create foreign key fields in the appropriate tables to specify the relationships between the tables. 

Finally, we navigate to the Relationship tab of the Manage Database dialog and recreate our ERD using the new tables we've created. So as not to generate a duplicate path to the same Table Occurrence, we’ll have to make a Table Occurrence Group for both contacts and companies. When we're done, it will look something like this:

And voila! We have the bones of our CRM solution, otherwise known as the schema of the database. Next, we'll finish up the FileMaker CRM by adding a user interface (UI).