A complete guide for a project level M&E database
As a Monitoring and Evaluation practitioner, you are required to have a comprehensive overview of one or more projects in your organization. You need to be able to monitor the performance of each project, evaluate their impact and inform all relevant stakeholders about them.
To be able to do that you develop a set of tools such as:
- a Theory of Change (ToC)
- a Logframe or Results framework
- an M&E or MEAL plan
- data collection tools
You most probably have a clear understanding of the results you want to achieve through the project, which activities you need to implement, targets and indicators too.
However, once you start implementing the project’s activities, you might get to the realization that this is not enough. You and your colleagues might find data in different locations and formats, duplicate or outdated spreadsheets might still be moving around between stakeholders and answering key questions about your project starts becoming a daunting task.
In this guide, we will take a look at the concept of a project-level database to simplify your monitoring and evaluation efforts. We will explore:
- why and how such a database will help you get a better understanding of the project’s data
- key characteristics of an effective project-level database
- 5 steps for setting up such a database
- how to design a simple database in ActivityInfo
For a deep dive into the principles of database design take a look at our webinar Database design principles and designing a new database in ActivityInfo
If you like this article don't forget to register to the ActivityInfo newsletter to receive new guides, articles and webinars on various M&E topics!
You can read this article in French and Spanish
1. Why build a project-level M&E database?
A database allows you to store and manage data related to your project in one place. In comparison to spreadsheets, databases are more flexible and allow you to work with much larger volumes of data. Let’s see how this makes a difference during the project’s implementation:
Why replace a spreadsheet with a database?
It might be worth investing time and effort on designing a database in place of a spreadsheet to:
- Maximize efficiency in data workflows: this way duplication of effort is minimized and you can maximize the value of each data point stored in the database.
- Minimize the overall storage and size of the database; consequently reduce the cost of maintaining the database and improve overall performance.
- Minimize the need for costly and time-consuming future restructuring, if the initial structure proves insufficient for new requirements.
A database enables data-driven collaboration across all project stakeholders
When you start working on a project you are surrounded by various stakeholders with different needs and questions which can be answered with the data of the project. A mapping exercise will help you see clearly who these stakeholders are for your project. Examples include:
- Project officers implementing the project
- Project managers overseeing implementation
- Program leads and Senior leadership
- Donors
- Partner organizations
- Headquarters and other organization departments
- And of course beneficiaries
To be able to answer such diverse questions you need to have access to a single source of truth and to be able to manipulate it in such a way that you can get relevant answers.
Thanks to a database, stakeholders can take action on the data with confidence
When you have a single source of information in a database, you can always have an accurate description of the latest state of the project. You can:
- Summarize data to understand the overall performance.
- Sort data to detect high performing segments.
- Filter through the different segments of information to understand how results are broken down across different demographic groups or locations.
- Harmonize results across a standard set of indicators.
- Understand relationships between data and reveal insights.
- Track changes across time.
As all stakeholders can access high quality data when that’s needed, everyone involved can take actions with confidence.
2. What does a good project-level database look like?
In order to get the best out of a database, it needs to be designed in an effective way. Before starting the design of a database, we need to establish that there needs to be a balance between two overarching principles related to it:
- Principle 1- Minimize data redundancy: data added to the database must not be repeated unnecessarily
- Principle 2 - Maximize data integrity: data collected is covering all the elements needed for a holistic understanding of the different assets of the project
To further refine the database, we need to answer the following questions:
| Questions | Actions and Examples |
|---|---|
| What data do we want to include in the project database and how are they linked to each other? | Looking at the ToC, Logical Framework and M&E plan, we need to list all actionable data that should be captured in the database. Then, we need to map the relationships between these data points. E.g. Activities, locations, implementing partners, indicators, inventories, beneficiaries, etc. |
| Who can access that database? | Next, we need to define clear rules on who needs access to which pieces of data and what they should be able to with this data. E.g. Project officers working on daily activity implementation need to enter data about activities but project managers need to be able to work with the data to create reports. |
| How will changes be managed? | External or internal factors might cause the need for urgent or frequent changes on the data collected. This can have a great impact on the database design, if it’s not flexible enough to easily adjust to these changes. E.g. Training activities during COVID: new needs for data disaggregation between on-site and online in the middle of the year. |
| When will data become available to each stakeholder group? | An efficient database makes sure that data is captured and can be made available in a timely way to serve diverse reporting needs of stakeholders. E.g. Reporting: weekly for project officers, monthly for project managers, quarterly for senior leadership. |
3. Effective project level database design in 5 steps
Moving a step further, let’s take a look at 5 key steps for putting a project level M&E database in place. This is a helpful tool to start from scratch or to help you move from a spreadsheet to a database.
| Steps | Actions and Examples |
|---|---|
| Step 1: Identify entities | List all entities (the basic building blocks) involved in the project. The ToC and the LogFrame can help you identify all of the necessary entities to include in the database. E.g. Entities: Beneficiaries, Training courses, Training sessions |
| Step 2: Identify attributes | Take a close look at the needs of each stakeholder to understand what kind of data is needed for each identified entity for each stakeholder. Note any requirement for disaggregated data as you would need to collect that too. Think of the data type needed for each attribute (e.g text, quantity, list, date, etc.) E.g. 1. Entity: Training course Attributes: course name (text), instructor (text), location (with disaggregation online and on-site) (list of options) 2. Entity: Training sessions Attributes: date, participants, number of participants |
| Step 3: Identify relationships | Map how entities are related to each other. Note the direction of the relationship with arrows. Note the cardinality (one-to-one, one-to-many, many-to-one and many-to-many) in these relationships. Find your preferred way to visualize the data model. E.g. 1. One Training course has many Training sessions and one Training session has many Beneficiaries. 2. Each Beneficiary can attend multiple Training sessions. |
| Step 4: Assign key(s) | Define which attribute uniquely identifies each entity. Is it one attribute or more? If there are more, then you can have more keys (called ‘compound key’). E.g. Entity: Beneficiary Attributes: Name, Date of Birth, Location of Birth. Key(s): Name & Date of Birth |
| Step 5: Normalization | Optimize the database and establish clearer linkages between the available data. Instead of putting all data together, split it into more tables and ensure you don’t repeat it. Use keys to link tables together. This helps eliminate redundant data and improves integrity of data. Keep in mind the following: - Each attribute should have only one value. - All other values must be functionally dependent on the primary key. - No transitive functional dependencies (i.e. the other attributes shouldn’t be dependent on each other but should depend only on the key) |
4. Create a database in ActivityInfo
ActivityInfo offers an end-to-end solution for collecting, managing and analyzing data. In addition to collecting data using a mobile application, you can import data from other systems so as to manage it more efficiently.
Mapping the database design steps to ActivityInfo
In ActivityInfo, each database can contain folders with data collection forms. Using the data collection forms you can collect data related to the entities of your project –here called records. When you design data collection forms, you use fields to collect the Attributes that define the Entity and to define the keys for the database. Subforms allow you to create one-to-many relationships between parent and child records. Reference forms allow you to create standard lists which your data collection forms can refer to.
| Level | Rule of thumb | Examples |
|---|---|---|
| Database | A dedicated space for a discrete team with a specific use case. | One database for each country office. |
| Folder | Collection of forms relating to a common theme. | Forms grouped into folders by sector. |
| Form | A specific data set representing a list of entities each having a common set of attributes. | Beneficiary registry, Baseline Survey, List of Partners. |
| Record | An individual, discrete entity. | Beneficiary, Partner, Activity. |
| Field | A specific attribute that describes the entity in some way. | Name, Location, Date. |
Tips for creating a database in ActivityInfo
The following tips are useful if you are creating a database from scratch in ActivityInfo:
- Create reference forms for standard lists that will be used in multiple places; add these forms into a Folder to keep them together.
- Use Subforms to capture one to many relationships. This is handy, if for example you collect repetitive information for an entity.
- Automate various processes to minimize error and ensure data integrity: use input masks, validation rules and calculated fields.
Get started with your own database!
We offer various database templates which you can use to explore the steps discussed in this article. To practice with data similar to the data used in this article, you can use the Trainings monitoring database template. The database includes two reference forms where you can add the Trainings and the Training Institutions where trainings are conducted. It also contains three data collection forms; one for measuring the beneficiary outreach and collecting the participants list, one for a post training evaluation survey and one for creating an inventory of all the qualitative material related to the training. To get started, click on “Get started with the template”.
You can also design a simple database from scratch. To get start follow the tutorial “How to create a Blank (simple) Database without partners”.
You can try the ActivityInfo software and experiment with the databases with a 30 Free Trial. Do you wish to learn more or extend your Trial? Then, don’t hesitate to contact us!