Database EAV model in AppSheet?

Jonathon
Participant V

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:

  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 24 931
24 REPLIES 24

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โ€ฆ

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.

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

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.

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โ€ฆ

LeventK
Participant V

@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.

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

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.

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:


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.

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

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

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โ€ฆ

MultiTech
Participant V

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.



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!

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.

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.

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

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

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.

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

Can I sit at the kiddie table?

Maybe AppSheet should do a community showcase and interview app builders about one or more apps theyโ€™ve made, discuss design decisions, and post a thread on these forums to allow community discussion.

Could be a reoccurring feature.

@Peter

Yes absolutely, love the idea @Jonathon. Weโ€™re glad to work on coordinating a session of creators sharing some featured projects - if you or others have ideas for what youโ€™d like to see in a session like that, letโ€™s start a separate thread about it.

Also, weโ€™ve been considering a possible switch to Youtube Live for some of our webinars/demonstrations - what do you think of that as a channel for something like this? One reason is the improved accessibility, including ability to embed live or recorded versions in the community.

@Jonathon and all, coming late to this, but realized I had prototyped an app that might help here? As I read through this thread a theme that popped out at me was all of the column-centric and schema-centric comments. As many posted, youโ€™ll pull your hair out trying to manage a constant onslaught of one-offs in this sense.

I have an app which is โ€œrow-centricโ€ and attempts to mimic an object-oriented design, with โ€œobject typesโ€ from which you create โ€œinstances of those typesโ€.

The big downside here is that this solution leveraged the AppSheet rest API, which you may not have access to. Let me know if rest API is on the table here and Iโ€™ll write up the solution.

I also made a copy of the app, stripped out my api key, and published it here so that folks can review the rest of the design:

https://www.appsheet.com/samples/a-method-to-instantiate-instances-of-predefined-object-definitions?...

Top Labels in this Space