Database EAV model in AppSheet?

[Jonathon]

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