More fixed backend database structure after modifying form

What is the general goal of the feature?
More flexibility in back-end database structure of Aggregate when a form is modified

What are some example use cases for this feature?
We are trying to build complex calculations on our aggregate data that are often not covered by standard visualization software in the market (at least, that is my impression). This is a difficult process because when we build our API's for connection with Aggregate and the visualization portal, our forms need to be right: If we change it later on, we have to also change our API-calls because the database structure changes (an entire new database is being generated)! This is very inconvenient. It there a way to keep the database structure (and preserve our API calls), even though we modify the field form (add a question for example)...?

What can you contribute to making this feature a reality?
Yes, financially. I am trying to arrange funding for development. I see two options: Build on ODK or plug into an existing opensource software package. Would be great if the ODK community could advise me on this.

Very interesting feature request, Eddy! A couple of questions for you.

  1. When would you need this to ship by?
  2. How big is your Aggregate install currently (forms, users, submissions)?
  3. What open source packages are you considering on building on?

Hi Yanokwa,

Well, we are fully working on our visualisation and API's this so the sooner, the better:-). I have a lot of data (forms) on our aggregate for several projects that I want to disseminate through a website. I the future I also would like to do trend analysis. As such, a bit more flexibility with building field forms while maintaining database structure is important. The struggle is now building and testing the forms, since they have to be perfect (if we change them later on, our API's are useless). I am now considering AKVO, since they have the same mindset as I have (open source/not-for-profit, keep it affordable, openbox, etc.) or SurveyCTO. However, these packages also seem to promise things that are not fully compatible with our needs. However, I consider to plug into either ODK or into their software packages. I don't know yet what is most strategic/efficient and if/how this works for these software providers... I think the database structure is one of the biggest bottlenecks for me right now. Advise is very much appreciated: Will it be possible within ODK and if so; what would be the best way to do it?

I hear you that you'd like a backend that supports form changes and I'd love to have that too in the core tools, but it's a big change that will take time to implement.

We do have plans to take on this problem, but we have to put a few more things (code and governance) in place first. I'm hoping we'll be able to lay that groundwork in the next few months.

I understand that you might not be able to wait, so if you need something today, Ona is open source, supports ODK forms, and provides some of the flexibility you describe.

1 Like

As does Smap of course :). You can change any aspect of your form except
the type of a question.

1 Like

Hi Yaw, Thanks for this feedback. What would be a rough estimation of finance needed for such a change? I just need a rough figure because I am in a fundraising round now and want to include this into the budget.

To answer your question: Yes, that is what I would like to have. So in case I change a form at a later stage in the project, the database should not directly be affected (changed) because this is making my API's to get useless (and with that my visualization of the results). A more stable database would also make visualization more easy I think. Could you also indicate how much time it would take to build this (estimation) in case finance is available?

The feature description has too many unspecified details for me to produce a useful estimate!

For example, if your reporting tool needs to talk directly to the database, then that's a lot harder than providing some APIs that get you "group" different forms together. Or maybe for your use case, if you need direct database access, maybe database views would work?

What I'd recommend is that you and perhaps a developer from your organization detail what you'd like to see. Once that's available, estimates of effort will then be feasible.

Oke. I understand. I will draft the requirements later on when with an IT-er. Thanks for this feedback!

Hi Neil,

Thanks for this feedback. Sounds interesting. In the end, I would like to have an entirely stable database. I am not an IT-expert so I don't know if this is 100% feasible. But with ODK it is inconvenient that visualization is hard because of these database table changes. The reality is that forms often have to be modified in time, also by including questions or changing the type. However, visualization is becoming very important, so a more stable database would create so much great opportunities. I am now trying to find out what is the best way forward: Plugging into existing (opensource or not-for-profit) software or help ODK to develop further. My impression of most monitoring tools that exist on the market (including SMAP), is that ODK is often the core backend of these software packages. Am I right?
Efficiency (quick development) en quality (something that reajly works for us) is important while I really would like to contribute to the development of opensource tools (or at least a not-for-profit tool) instead of a 100% commercial one. The latter tend to be lockedin boxes or becoming too expensive. So this is what I try to figure out: what is the best way forward with the finances I have. Maybe you, Yaw or any other can help me with your independent advice.

Hi Eddy,

I agree that stability of the table structures for storage of results is a
good design objective. We are getting a lot of applications where people
are moving beyond one off assessments and into ongoing collection of data
that evolves over time. Also more events are being attached to surveys,
for example sending an email when a specific result value is submitted or
creating a task for someone to complete a form in response to a
submission. Setting these events up again every time you change and then
reload a form definition is painful but can be avoided if you are able to
just modify the existing survey.

Aggregate has some capability to do this I think, Yaw will be able to
elaborate more. Smap has the ability to modify a form using it's on line
editor. The next release, due out before the end of the year extends this
capability to loading xlsforms. So you will be able to replace an
existing form with a new xlsform and it will reuse the data columns that
match. You will also be able to group multiple forms so that they share
the same set of tables. This is a great way to implement processes. A
task generated for Form B can include data from Form A if they share the
same database columns. You can do this now but only through the online
editor, the new xlsform interface will make this a lot more useful for
complex forms.

Yes correct ODKCollect and the Java Rosa APIs are a core component of Smap
and will continue to be. We don't use Briefcase or Aggregate though and
after the next release we won't be using pyxform. The source code for Smap
is mostly open source but there are a few exceptions related to AWS
integration. However unlike ODK we don't have a development community and
I haven't got any plans to set one up due to the management overhead. So
contributions are welcome but its easier to get things done in the Smap
world by hiring us and we then release the changes in the open source



Thanks for this informative feedback Neil. I will consider this. As you say, I prefer to develop in open source because the funding I have is generated by society and as such should benefit society. On the other hand -as you mention- I am very on "getting things done" and have results within a certain time frame. So I am also looking at some flexibility in the development. As a non-IT person I don't know exactely how the open source community works in the development of software for example how hiring works and how we make sure the new software improvements will benefit (are flexed) the open source community.

FYI: So far, we have build our monitoring database within Aggregate and created API calls to this database. There is substantial time effort in this, so I prefer not to move to another database. But if we have to, at least have the opportunity to transfer the data from Aggregate to the other database.

So, (maybe also a question to Yaw), how could we start? I have a list of improvements that I would like to have (preferably for ODK Aggregate because I am using this now) but not all of them may be interesting, expecially for certain software developers such as SMAP or ODK who might have their own interest. Or are we not/less restricted/limited in case we pay for these developments?