(TL;WR: Is it possible to specify a relational schema in ODK Central or any other ODK product or ODK clones?)
Hi all! So I think my question has been asked too specifically in the past (for example), so I'd like to ask a very wide question.
Let's say I am working with a government who is teaching farmers some new techniques and then in some time they will go and see if they have adopted them. I could create one big form to collect all the information and have columns be empty if the information doesn't fit (like have training empty if I'm looking for adoption), but that would result in many many rows with empty columns. So I might suggest a schema (or way of arranging information) with few tables and these fields:
- Village: Village_Name
- Person: Full_Name, Village
- Training: Person, Topic, Date
- Adoption: Person, Topic, Date, Applied_yes_no
- Topic: Name
The schema might look like this, where each arrow shows that information is referenced from the table that it points to:
Now we can design an ODK Collect form to capture data from each form independently, with no blanks.
But if I wanted to reflect the fact that my People table is used in both Training and Adoption so that enumerators wouldn't have to capture name as a short-text, but as a drop-down list, what are the solutions for storing this information relationally?
Note 1: I'm not asking about how to organize the information in the XLSForm, that part is simple enough to do manually by copypasting for one collection. I'm asking if it's possible to set up the relationship at the storage side.
Note 2: Let's ignore the additional complication of "offline data not being available until sync" and other detailed issues.
Is there any ODK product to let me store this information as I have drawn? How do others solve this problem?