00:00:00 Introduction

Hello everyone. Good morning, good afternoon, or good evening, depending on where you are dialing in from. Thanks so much for joining us today in the second out of our four-session series on getting started with ActivityInfo. For today, we'll take a look at designing your first database.

In terms of what we will learn in this session today, basically, it will be a very practical session on how to build your database in ActivityInfo. Within that, we'll take a look first at how you would go about creating a data model which will serve as the blueprint for your database. Then, we will look at how you would go about translating that data model into an actual database in ActivityInfo. Following that, we'll do a live demonstration on actually building out an example database within the system itself. We will make sure to include some best practices and tips as well in this session for you to take away and apply in your own databases.

For this session and the next session, we want you to follow along with a project that you're currently working on. So have a think about something that you're currently working on. What kind of data do you work with for that project? Try and apply the principles and the steps that we'll cover today in your own project. Hopefully, you can follow along as well in the next session when we're analyzing data so that the database that you create after today's session, you can then carry over into the next session where we'll start to create some of the reports and analysis in ActivityInfo.

00:02:15 Designing a database in ActivityInfo

All right, so without any further ado, let's jump right into the first part on how you would design a database in ActivityInfo. The process that we would follow overall contains two parts. The first part is your preliminary stage of building your data model. This is essentially the blueprint where you plan out and sketch out what components are required in your database before you then proceed with the actual construction of the database in ActivityInfo. That is part two, where you translate what you'd sketched out in your data model into an actual functioning database, complete with the various forms and specifications. We're going to take a look at both of these parts in turn starting off with part one, building out your data model.

It's really important to make sure you invest the time in this first part. Naturally, before you go about constructing your house, you draw the plans first; you create a blueprint. This is where you first organize your approach for how you would construct your house, and in this case, the database. You might have a spreadsheet already in hand with the data that you know you want to capture in your database, and you might think it's just a matter of uploading the spreadsheet that I've already got into the system. However, you'll want to first take a step back, take a look at what data you have, and do a data modeling exercise to first organize your information.

You'll need to do this exercise because you want to maximize the efficiency of how the data is organized in your database. There are additional benefits to this as well, including minimizing the cost of storing your data, improving the performance of your database, and making sure you have the right structure in the first place that would then prevent the need for you to restructure your database in the future. Ultimately, we need to think about the data model first in order to minimize the redundancy in our database and make sure that we have a high level of data integrity in the data that we are capturing.

00:04:30 Part 1: Building your data model

In terms of creating our data model, we want to present to you this five-step framework for how you would go about creating that blueprint for your database. We have a full webinar that we've done previously on database design principles where we go over each of these steps in more detail. We won't have enough time to go through all of that in detail in today's session, but let me briefly walk you through what these five steps are.

Step one is about identifying your entities. These are the different categories of data that you want to capture in your database. Think about what are the things that you need to measure. What are the objects that you work with in the implementation of your project or your program? Those are your entities that have information captured inside them.

Once you've identified those entities, you need to then take a look at what attributes about those entities exist. When we say attributes, these are the different facets of that data entity that describe something about that entity. For example, when you're talking about entities that describe a person or an individual beneficiary, they might have attributes that represent their names, their date of birth, their sex, the location in which they reside, and so on. Step two is all about listing out what are those attributes that you want to capture for each of your entities.

Step three, once you've identified the entities and the attributes, is to identify the different relationships that exist between the different entities. How do they relate to each other? Are they connected in some way? You might have a list of beneficiaries whom your project is serving, and you might have the different services that you offer to those beneficiaries. Is there a relationship between those two different data points? What is the nature of that relationship? Could you have multiple services that are being delivered to an individual beneficiary? Step three is mapping out what relationships exist and understanding in a very deep way the nature of those relationships, thinking about concepts such as cardinality.

Step four is about assigning keys. Keys are a way by which you can identify the unique individual records within a particular set of data. Think about the attributes that you identified in step two for each of your data entities; which of those attributes would give you that information to determine whether this particular record is unique? When you're talking about a data set of individuals, for example, it could be that their name is the attribute that uniquely identifies the individual, and that's what you would assign in the key.

Finally, we get to step five, which is normalization. When we talk about normalization, we think about the organization of all of the data sets that we've identified and thinking about the most efficient way by which we can organize those different data sets so that we are not repeating information in multiple places. We're capturing key information in one place and connecting other data sets thanks to the relationships that we've identified in step three.

Let's take a practical example to help illustrate this. In this example, we'll imagine a project to do with child protection training. Let's take a look at what kinds of data you might be working with for such a project. We might have some beneficiaries, training courses, and then data about training sessions. For each of these boxes, we have listed a couple of attributes that describe something about each of those entities. We've also noted here what we think the format of those different attributes should be in. Some of these are text fields, others could be date fields or quantity fields, and others could be calculated or referenced from other data sets. An important part of this data model is to identify what relationships exist between those different sets of data. For example, training sessions will have multiple beneficiaries attending them, and a particular training session would be linked to a specific training course.

00:09:15 Part 2: Translating the data model to ActivityInfo

Once you've got the data model, now we're ready to actually log into ActivityInfo and build our database in the software itself. For this part, it's useful for us to take a quick look back at the hierarchy that we presented in the first session where we looked at the different levels of where the data sits in ActivityInfo. The most basic unit is the record, and for the purposes of our discussion today, the record is what maps back to the concept of data entities. For each record, you have fields, which are the attributes that describe something about that record. Records are collected in forms. If you've got multiple forms that relate to each other, you can organize them in folders, and everything sits within a database. Your database is your overall container for all of your data; it's essentially the workspace where you collaborate with others.

So, what are the steps that you'll need to do here? First of all, you'll need to create a form for each of your data entities. All of those entities that you identified in your data model correspond with a distinct form because each form in ActivityInfo has its own set of specific fields and represents a particular data entity.

There are a couple of considerations within this step that depend on the relationships that you identified in your data model. If you've identified one-to-many relationships between a couple of your data sets, we would recommend creating a reference form to implement those relationships within ActivityInfo. This is how you are able to connect different data sets. For example, if you have a reference for a list of locations, that's a standard set of data that you will need to connect with many other sets of data in your database.

You might also have many-to-one relationships that you've identified. It could be useful in these situations to create a subform to capture that kind of relationship. You might have, for example, for one particular training session, many beneficiaries who attended that training session. To be able to associate all of those beneficiaries and capture their attendance to that particular session, you can create a subform so that allows you to create a sub-record for each beneficiary who attended that training and associate them with that particular training session.

Once you've got the forms and fields all configured, then you'll need to think about how you will collaborate with your coworkers, your colleagues, and your partners on the same database. Another crucial part of this translating step is to configure user roles in your database. First, identify what roles are needed in your database. Think about the different workflows that you have in your team. Who is needing to submit data, who is needing to validate data, and who is needing to review or make changes to the data? Identify what those roles are and then for each role think about what specific permissions are needing to be granted.

Finally, here is a summary of some rules of thumb for you to keep in mind as you're building out your database. When you're looking at the database level, ActivityInfo allows you to create multiple databases. Normally you mostly need one database to capture most of your information. A database is a dedicated space for you to collaborate on all the data that you need. For example, you would create a database for each country office. If you have different country offices, they are working separately from each other with different sets of data, so it might make sense to have a dedicated database for each of them.

Going into the next level, folders: you might have work that is grouped thematically. If you're an NGO that implements programs in different sectors, you have very different kinds of information that you collect about each sector. You might want to group your content into folders separated by sector. Forms are the specific entities of data, such as beneficiary registries, baseline surveys, or lists of partners. The record is the individual entity that you collect within the form, and fields are the specific attributes that describe something about your record.

00:14:45 Overview of form design and field types

Thank you very much, Jeric. Before jumping into our live demonstration, let's have a look very quickly at the form design and the field types and how you can translate your data model into ActivityInfo. When you log in to the ActivityInfo platform, you can see the databases. When you enter the databases, if you have already created one, then you can see the "Add form" button. When you click add form, then you can see "Add field."

There are 17 field types available. You can use the relevant field here based on your requirement and questionnaire or data types.

When you click any field type, you can see the settings panel.

00:18:30 Live demonstration: Building the database

Let's go to the live demo session. I will open the ActivityInfo form. I'm going to enter the Training database. Let's say we are going to create one project file here. You can create your folders here according to your structures. Let me create one thematic folder: the "Protection Project." Under the Protection Project, you can create your country folder, for example, Iraq.

Now I would like to design my data collection forms for some specific indicators. I am going to create:

Let's create the first Beneficiary Tracking Form. I click on "Add form" and give it a name.

Now let's move to the next form, the Training Course Form. I would like to add a list of training courses within the protection project.

I will record one data point here: "Protection MHPSS training" with instructor "Jeric."

Now, I would like to create the Training Sessions Record form to record the training participants.

I also want the list of participants, but I don't want to type them manually to avoid duplication. I will create a Subform for the participant list.

I want to set a Validation Rule here. People can participate in multiple sessions, but they can only practice knowledge from sessions they actually attended. I will set a validation rule on the "Practice" field. The formula logic is: ISBLANK(SORT(SEARCH([Practice], [Sessions]))). This ensures that if they try to select a session for practice that they didn't select in the participation question, it will not allow it.

Let's record data to see how it looks. I select the training course (referenced), enter the date, and upload a photo. Then I add a record in the participant list subform. I select "Ganesh Thapa" from the beneficiary list. I select that I participated in sessions A, B, and C. If I try to select session D for the "Practice" question, it will not allow me because I didn't participate in session D. If I select B, it works.

This shows how you can translate your data model into ActivityInfo. Whatever complexity you have designed, it is possible to set up here.

00:38:45 Conclusion and next steps

Great. Thanks so much, Ganesh, for that really helpful demonstration. I hope that gives everyone a much clearer idea of how you would go about translating your data model into a functioning database in ActivityInfo.

That brings us to the end of our presentation today. We will be staying for a few more minutes in case anyone has any questions. We wanted to call out that if you do have some more questions that we won't get through today, we do have an Office Hours session on February 21st. We know that you must have more questions that you'll encounter as you go about designing your own databases for your own projects, so please do feel free to sign up for that session.

Next week will be focused on how you can analyze your data that you've captured in your database. So do sign up for that as well if you want to learn more. With that out of the way, we can open up the floor to our Q&A.