I have an inspection application, with a handful of distinct inspection types with associated unique tables. One common element of these inspections is photos, of which for any given inspection there can be many photos.
I would like to avoid creating a photos table for each unique inspection type - it would be preferable to have a singular photos table to which every inspection attaches its photos. This is preferable for both simplicity, as well as it would facilitate having a singular ‘photos’ view instead of multiple.
Problem: the pattern to do this would require the photos table to have a column for every inspection type. For example:
I’m curious does anyone have a solution to the above issue? Would it be possible to simplify this by having an inspection_type column, which would direct the foreign key to the appropriate table? as below:
photos_table
| id | insp_type | insp_REF | photo |
Even if this were possible, would it work while allowing me to attach photos directly within the inspection forms (as with the typical related tables pattern)?
One solution to this would be to have a parent table [insp] with related tables for inspection types, and a singular related table for photos… This suffers from a similar problem as above, where my [insp] table would need multiple REF columns to refer to the various inspection types!
Maybe there is some other option I have not considered? Maybe I’m missing something?
I have created few apps in a way that one data table covers all different type of inspections. Then you would need only one photo table. For this approach you would need to have a separate template table where you have all questions and possible dropdown values as well.
I’ve used that pattern for a safety inspection app before. You are suggesting 3 tables: inspection, inspection_template, photos
where the inspection table may be made up of several generic columns, say q1,q2,q3,...,q4, which are given meaning through the inspection_template table?
I’ve considered this; unfortunately I think its only a viable option when the inspections share at least some resemblence to each-other. In my current case, the various inspections can be vastly different in terms of length, format, validation rules, and a combination of the above.
At this point in time, I think I have identified the two best options, for my use case, while working within AppSheets constraints:
Create a photos table for each inspection type. For each photos table I will have to create an additional ref column that will pre-fill with some common identifier, say a project code. Photos will retain their reference to their respective inspection, and I will be able to group them all by the common identifier for an overall photos view. PRO: There may be some performance benefits in querying the pre-filtered photos table, should I ever need to CON: I will be doubling the volume of overhead tables
Create a singular photos table with an additional column for every inspection type PRO: Only requires a single table PRO: Could associate a photo with more than one inspection type CON: It will be a very wide table, with several null values
Hi @Jonathon, I don’t like option #2. If AppSheet is to do a decent job of understanding your intent, you want the data model to reflect a true entity-relationship model. In an ER model, each entity has properties. Now we don’t support entity sub-typing. So you have to take each entity type (in your case, each kind of inspection) and make it a separate table. And that table should have a Photo property (or it can have a nested set of related properties in a dependent sub-table).
Then if you want to see all the photos together in one place, the challenge you have is how to express this as a slice of some form. Our current Slice mechanism lacks this expressive power, but you can see how it will in the future (with Unions).
My general view on these things is that AppSheet steers you towards a certain logical way to build apps. If you hit limitations in expressive power, we prefer you live within them (or push us to fix them, which we will). But if you work around them in “unnatural” ways (at least from the point of view of ER modeling) then the platform will likely not work as well for you.
Hey @Jonathon I know its been a few years since this issue, but I presently ran into it again today. I was wondering if you or anyone had more solutions to it at this time. Mine is an HR app with attachment table issues instead of your photos issue. I believe the issue still doesn’t have a resolution, but wondering what you have come up with to circumnavigate it. Any help is appreciated. Thanks!
Appsheet restricts each table to a single ispartof reference, so there is no way to have a shared child table that allows adds through the parent form.
The best solution I have is to structure the app where adding photos/files occurs outside of the form.. not always ideal but neither are the alternatives.
This data structure will work well outside of forms:
Thanks for the update. Yeah… that what I ran into as well.
I might create database views out of a “general attachments” database table for each table that needs an attachment table and add those views as tables to the app. This way each table that needs an attachment table has a referenced view that they can add directly to in a form.
This way its all writing to the same “general attachments” database table but presents as different data. I can load the “general attachments” db table to that app or other apps and can pull the data or present the data as a single resouce that can be edited or presented but grouped by the different tables that created entries… Idk, was hoping it could be more simple…
Thanks to both @Jonathon and @dbaum replies. It is helpful to know there is no good solution yet
