Project Everest

Proposal (Operational/Other)

[Proposal: Operational/Other]: FarmEd All Countries - Unified Database Structure - January 2018

Gabriel Raubenheimer
Gabriel Raubenheimer | Jan 16, 2018 | in Ideas Box

As our spreadsheets grow fatter and fatter, the FarmEd Fiji team is hard at work building databases to supplement/replace them. This is important because it allows for much greater integration with other programs (crucially, the FarmEd app).

From Cambodia, here are some important ideas about that design, following on from a conversation between the two countries, with the aim of expanding that discussion and opening it up to you beautiful people.


International Focus

Having a single database that covers all four FarmEd countries is essential. It's less time intensive, less resource intensive and far more powerful. I propose that a conversation needs to take place between FarmEd members from all four countries to determine what data each team needs to be able to input. This can help the fantastic team implementing the software to design as effectively as possible.


Consolidating Formats

Building the database (with an international focus) has an ancillary but equally important function - it forces us to consolidate the disparate Sheets formats we're using to store our data. Designing the database and implementing it are two distinct tasks, but I suggest that the Sheets we continue to use to store data until the DB is up and running should mirror the database design and should be consistent across all countries. This is important because:

  • It makes transfer from Sheets to DB far easier
  • It allows teams to see what each country is doing, and integrate these ideas into data collection
  • It makes pre-DB data analysis infinitely easier!


Booleans Are Beautiful

Booleans (true or false, binary data, ones or zeros) are very easy to work with, whereas text is often not. A lot of data can be stored with booleans, and I suggest that we do so. For example, instead of a table with a column labelled 'Crops' that lists all the crops a farmer grows, we can have a column for each species, and input true or false for each farmer. The list of species being investigated is fairly limited, so this is not hard to do.

Technical notes:

  • This prevents us from dealing with delimiters etc. when querying the database, as we'd have to with string/text fields. It also lowers the risk of spelling mistakes etc. damaging data
  • SQL is highly efficient so multiple fields should have no effect on efficiency/speed of queries
  • This could make data input more difficult, but good front-ends will alleviate this
  • Numerical data is also beautiful and easy to work with


Putting It All Together

Finally, Field Dev, PoC and Market Analysis are all collecting data constantly, so we should most certainly have the end goal of having them all together! This is pretty self-evident, but I propose that the design for each is considered and consolidated now. This can just mean making sure, as above, that each country has identical templates for data collection.


As a final note, the FarmEd Fiji team working on implementing this are doing a fantastic job. It's not an easy thing to do, and will take time, but it's worth thinking about these things now.

What do you and your team need to put in a database?

edited on 6th September 2018, 01:09 by Justin Hakeem

Matt Allan Jan 16, 2018

Nice work, mate.

On booleans, agree its simpler. Would recommend recruiting a 'change champion' or the like in each country responsible for implementing this through January. With so many input sources, we'll need to develop (in conjunction with the change champions) a 'Data input guidelines' document in real ELI5 language to standardise any input before entered into the cloud database.

Have you considered how you'll host this? You mentioned Azure previously - is this already implemented? I have a bias and preference to AWS personally.

Reply 3

Gabriel Raubenheimer Jan 17, 2018

Thanks for that Matt. Fantastic idea re. change champion, that's definitely something I will be working out today. I think you've hit the nail on the head though - that will be a highly effective way to implement.

Totally agree in regards to guidelines as well. At the moment we are looking to use Google Forms connected to MySQL through Zapier. That will make input as simple as possible, but as always, if you have any other ideas, please share.

As of now, it will be hosted on AWS - app integration etc. seems very solid.

Reply 0

Ciaran Hoare Jan 21, 2018

Hi Matt, I'm keen to set up your idea of a 'change champion', or something similar in order to maintain comms between team members in each respective country. We will set this up once you arrive in Cambodia.

Reply 2

View all replies (2)

Kate O'Donnell Feb 18, 2018

How easy do you think booleans would be to implement in our spreadsheets currently? Thinking about our data analysis protocol.

Users tagged:

Reply 0

Darcy Connaghan Jul 1, 2018

Status label added: Work Update

Reply 0

Wade Tink Jul 1, 2018

Status label added: Proposal (Operational/other)

Status label removed: Work Update

Reply 0

Wade Tink Jul 1, 2018

Adopted? Results?

Users tagged:

Reply 0