Art of the Possible #4: Databases
This is the fourth entry from Chris Wood in the "Art of the Possible: Technology and Tools for Improved Monitoring and Evaluation" blog series, which covers design, data collection, databases, analysis, and using your results. All articles cover the use of technology, the options available and how to decide on the best approach for your projects. This article explores how you can use databases in your M&E system.
When you collect your data, you need to store and manage the data in what is generically termed a “database.” Let’s cover a little terminology first.
A field (column) is the basic unit of data and stores a single piece of information (value).
A record (row) is a combination of fields containing data about a student, course or survey
A set of records with the same fields are collected together in a table:
As you can see, this looks like a spreadsheet with columns and rows.
Size is important.
When considering how to store and manage your data, you should always plan for the future and estimate how much data you are likely to collect. In our simple project where we are delivering 6 training classes to 500 students and performing a longitudinal survey to measure change, we can do some simple calculations:
500 rows – one for each student
6 columns to mark attendance at each of the training classes
12 columns (one for each survey answer) x 3 surveys = 36 columns
6 columns to hold the student data and unique ID
This is a total of 24,000 values (cells) per year and 120,000 over the 5 year program. As you can see, even for a simple example, the amount of data will quickly become difficult to navigate and manage.
Excel is a single-use database
- Only one person can enter data or analyse at a time
- Copies can be made but merging changes is not always easy
Any data can be entered into any cell
- Need higher level of expertise to restrict field values using validation functions
Analyses using formulae and charts rely on defined regions to provide data
- Adding more columns or rows means that definitions need to be changed
Moving up to a database from an Excel spreadsheet requires thinking in a slightly different way about your data. A relational database (Access, mySQL, etc) uses separate data tables which are linked using unique keys. Data is only stored in one place (normalised) and is easy to manage and navigate, and adding new data is easy.
Advantages of Databases:
- Scalable and can store huge amounts of data
- Able to quickly retrieve data on a particular student, course or program
- Many users can access the same data at the same time (authorisation)
- Open source databases are available, for instance mySQL (www.mysql.com)
- Applications built on relational databases are powerful, scalable and flexible (eg. Facebook, Hotmail, You Tube, Amazon)
Challenges of Databases
- The user interface is the critical element to any database - It provides all of the functions to enter data, create reports and to export the data
- The database only stores data. The application must consider how data will be entered, and how it will be analysed and extracted.
- The application must be simple to understand, easy to use and quick to respond.
- Coding expertise is needed to access the database
Cloud-based databases are simply a relational database hosted on the internet. You pay a small monthly fee and the service provider takes care of maintenance of the software and hardware, backups, and power outages.
- A database simply stores data
You must carefully consider:
- How to collect data and enter it in to the database
- How you need the data to be analysed, reported and exported from the database
There are many service providers who offer databases
There are only a few who offer database applications specifically designed for M&E
goProve (www.goprove.org ) is a cloud-based M&E solution which uses a relational database and allows unlimited student, project and survey records