Database EAV model in AppSheet?

AppSheet AppSheet Q&A
1

I have some users that want an app with dozens of unique and complex forms, and have been considering something like an EAV model to handle the data. Here are my thoughts:

2X_7_73e845039d50231e1690a38a74fa3391fb368b90.png
2X_7_73e845039d50231e1690a38a74fa3391fb368b90.png713×287 6.91 KB

  1. There can be n number of form# tables, each of which have a distinct column structure based around their form views.
  2. There is a single data table with a schema_id column and enough additional columns to hold the data from your largest associated form. Most columns are plain text / varchar() with the exception of some special datatype columns such as datetimes
  3. Each form has an OnSave event that adds a new row to the data table and casts all data from the forms as plain text. The data in the form# tables could then be automatically purged or alternatively the filter out all existing rows toggle could be enabled to prevent loading it.
  4. To edit the data, an action is created that references a linktoform() formula from a schema table which repopulates the specific form as it existed at save

Benefits:

  • To add a new table you simply create the database schema, form view, and add a linktoform() schema to cast the data back to the original form.
    • The converse of this requires the creation of several actions, and workflows, detail views, etc. which are specific to each table. If an app has dozens of unique form tables this becomes a lot to manage.
  • Filtering project or user specific data only has to occur on the data table instead of every form that may exist

Challenges:

  • detail views on the ‘data’ table would be wonky.
    • You could add a column to the schema table that holds a LIST() of column descriptions, and perform lookups to populate column descriptions
    • You could constrain the application detail views to simply be the downloadable PDF’s that are generated on a form-by-form basis

@tony @Grant_Stead @LeventK @Steve
Someone please tell me why this is a bad idea and talk me out of this

1 Like
2

Of course this is a bad idea…
Appsheet just isn’t geared for this kind of arrangement, be and you’ll be fighting it every day for the rest of your life…

3 Likes
3

Why are you not just making a table with ALL columns, then making a slice for each form?

I do like the idea of using the EAV model in an app, for tables that otherwise would have many columns, but you don’t want to burden your users to scroll through this huge form or think they have to enter info to each field.

2 Likes
4

I have production applications (high level daily safety paperwork) which use a single table of data and a reference to a schema table to dynamically change the column descriptions in-app. This model is great as it gives the appearance of having several forms, but it only really works when the forms are simple.

If you want to add additional layers of complexity (rows being shown, edited, or required based upon some condition of other answers specific to that form, or suggesting values specific to a formtype), then the aforementioned model becomes rather complex. You have me thinking now, if it would be possible by adding some additional tables to handle the dynamic form aspects. So you would have a schema_columnName, schema_editable, schema_required, schema_suggestedValues…

Maybe I can find a way to store my data as JSON instead.

Or maybe I’m trying to fit a square block through a round hole as Grant implied

1 Like
5

Jonathon:

  1. Each form has an OnSave event that adds a new column to the data table and casts all data from the forms as plain text. The data in the form# tables could then be automatically purged or alternatively the filter out all existing rows toggle could be enabled to prevent loading it.

@Jonathon
This step is completely not possible with AppSheet. Provided it would be doable, then there will be a cons that when even 1 column is added to Data table thru n number of forms, then the Data table will be a mess after a while.

2 Likes
6

Apologies Levent you’re right what I had written was impossible. I meant to say each form has an OnSave event that adds a new row

1 Like
7

I was not understanding before about the schemas, but I think I do now. I see what you’re getting at with all the name,editable,required… schemas. Very interesting!

Who are you envisioning would create the schemas for a new table? Why would there need to be a bunch of form views, could the form table just have a reference to the schema as well?

I think it’d have to be an awful lot of different forms to make it worthwhile to set this up.

8

I would envision that clients / users would develop forms / questions which would be submitted to me or other developers to add. There are a few reasons:

  • As a matter of QA/QC/Branding, the questions would need review and tweaking before being added. Also, we would want to prevent duplication of forms from different users
  • As the forms will vary slightly, each form may warrant a slightly tweaked workflow / pdf template

I can’t think of a way to make an app of this nature completely self service without introducing yet another layer of complexity which seems unlikely given i’m already skeptical on the idea.

As a concept, here is what I’m building:

In theory, there could be many forms - the only limit would be AppSheets ability to scale with them…

2 Likes
9

I think you have a good idea here.

I think one of your forms can be a table-building form, that instead of copying it’s contents to the data table, it copies to the schema table. But yes, I do also see duplicate form prevention would be an issue. And more complicated editiableIfs/showIfs/etc would be a pain.

I kind of want to try it.

1 Like
10

As mentioned, I have a working version for safety forms where every question is a yes/no. My organization had existing safety forms that were already constrained in this manner, so it was simple enough to digitize them. The safety team is also able to add new formtypes or re-issue new versions of existing forms, so long as they are constrained in a similar way. Despite having over 32 different forms and printouts, the apps ‘spec’ view still looks like this:

2X_e_e42d0eccf335f430f6af46c19442312731cfe1c0.png
2X_e_e42d0eccf335f430f6af46c19442312731cfe1c0.png519×715 13.3 KB

So, I can attest that the methodology is sound for the simple case. However, I don’t think I will be proceeding forward with the complex case anytime soon.

1 Like
11

Can you share that app with me? Curious how you went about it!

12

Sure Grant;

I copied the app and stripped it down to the basic concept; its lacking some polish as a result of this but you’ll manage.

Edit: here is the sample for all

2 Likes
13

Awesome, thanks! Just started poking around. I had attempted to do something like this before, but called it off due to it being locked in… Then I started rebuilding it “transactional.” So think each question is it’s own record…
So I had a Template table > Child Questions
The child questions had an order column…
Then when you started a new “survey” you would basically hit “next” Next" “Next” to keep answering all of the questions… It was a little annoying… I also came up with an idea in which you could use the “new add a row” feature to basically add all the transactional records and then take the person to the detail view after filling out the main set of info, and use inline actions to modify the “answers” It’s all just soo much work… lol (Maybe less work that making a new table/app for each survey…

1 Like
14

I knew I bookmarked this for later digestion.

@Jonathon I’m currently fighting with a system that I made for a client that does exactly this. The idea was to create a platform to capture survey data, but also to allow people to create the surveys, all the questions, the flow of things, etc. inside the app as well.

We created a table to define how each question should look in the form, then created a “Live_Form” that conforms itself based on whatever was specified for what type of data is being collected, how it should look, what values are allowed, etc.

We eventually included a way to pass information from one question to the next, so you could take the answers from one question and use them as options in another. We also make use of the “background record creation” to handle creating skip records; when someone skips a question we still want a record noting they’ve skipped something, but we don’t want to force the users to press “Skip… skip… skip…” to go through dozens of forms, so we create them in the background.

The data entry table is pretty wild; since you can’t dynamically change column types we instead had to include one of every type (well, almost; there’s a few we don’t use), then pull all the conforming info from the instructions table and show the one we need.

2X_4_4232ab445833ceb1ae86c97cd70987245735f65e.png

It all sounds very good and all… but this is not the way to collect data like this. Trust me. After we got into actually using the app, and real amounts of data started coming in, we came to the realization that we really need the data in their own individual tables.

Don’t get me wrong, the form works - and works rather well despite some HORRENDOUS looking formulas - it’s everything after that that’s a nightmare now. All the things we would like to do with that data would be a whole lot easier to accomplish if things were their own tables.

The idea is sound, even getting it working is possible, and honestly it would be a great way to solicit data from clients about what they want in their app. Like… you could give a tool like this to them and say, “Here, you design your forms, the questions, etc.” - then take that data and build a real table out of that.

But the time-saving aspect we thought we were going to get - where we didn’t have to create all those forms, with skip logic, formatting, etc. - we’re now making up for with having to come up with clever ways to handle data without making the app un-usable.

Hope it helps!

15

Thanks @MultiTech_Visions - I had been considering a model exactly as you posted. In essence, it would be no different than the example I shared with Grant above, except with extra columns available for different datatypes as you mentioned.

I think this idea goes off the rails when you try and allow people to define their own forms / questions within the app - Thats an additional layer of complexity that I have a hard time wrapping my head around.

If the addition of new forms was left to the app developer, you could slice the data from within the app to create the illusion of each form having its own table. One immediate benefit here is you could then have a single [photos] table that references the dynamic forms table, instead of needing an entire [photos] table for each individual table (each table can only be ispartof for one other table). If that makes sense.

Really interested in the navigation schema you’ve developed, with the left/right arrow buttons.

1 Like
16

Jonathon:

Really interested in the navigation schema you’ve developed, with the left/right arrow buttons.

There’s a lot going on to make that work… I’ll try and briefly cover it.

Those “View Previous answers” actions are looking at the navigation buttons for the form that was just saved: if they pressed the back button, the “View previous answer” action fires, if they press the next question button, the next question action fires, etc.

17

Also, forget about trying to chart your data via some sort of BI tool.

1 Like
18

You could still chart it, you would just have to slice the data against the schema type. I would argue, for multi-form applications, it would be easier to chart this data.

For a multi-tabled approach, you would have to set up new BI / chart structures for each dataset. For the pseudo-EAV approach, you would set it up once but with the preliminary filter on the dataset - you could do this in Excel or Power BI with pivottables pretty quickly

19

The client I made the system for used it to digitize 47 questionnaires; then they went out and started collecting data. (Over 1800 rows per respondent.)

Things uh… got out of hand really fast. So now the problem we have is there’s WAY to much data in one table, and in order for the client to use the data for any sort of analysis we have to slice it up into the separate 47 questionnaires; it takes time for the system to separate out all the records.

We’ve actually gone as far as to create a separate app to use for “Scoring.” Thankfully some of the questionnaires need further input after the data is collected from the respondent, so having a workflow to “Finalize” the data was actually called for.

In the scoring app, we have 1 table for each of the questionnaires (like we should have done in the FIRST place!) with a ton of LOOKUP() formulas to pull in each answer. Combined with some app formulas to check values, do pre-analysis calculations, summaries, etc. and it’s actually a pretty nice workflow… when it works.

There’s SOOOOO many moving parts that even minor tweaks cause catastrophic failure.

20

We should all get together and discuss / demonstrate our applications. Would be an interesting learning opportunity for us all!

Like a grown up show-and-tell

2 Likes