[Reviewer App] Setting Up Data Models

hello all, I recently came across this question from a user that I thought was really interesting and wanted to share (along with the response too) with a wider audience:

I have a specific question regarding the reviewer app I have been working on. I want to see if I am approaching this the wrong way or if maybe you know of a best practices.

I created a Material Additions app. In this app you select a Batch ID and then add materials based on a form. The form has you enter an Item Code, Lot#, Storage Conditions, quantity, expiry, location, UOM. As you add a material it saves all of this information into a Materials Additions table. Since you are adding materials for a single batch then when you add a second material it basically overwrites the row since the batch ID is kind of like a primary key. This now gets you the audit history for each material added.

When I built an app to review I have the user select a batchID from a table and then load that into a variable which I then link to the batchID column in my materials table. I then use a record history widget to pull the changes to the materials table to show the history of what I loaded. What I don’t like about this is it only shows what changes I made from each addition. So if material 1 and 2 have the same storage conditions then it doesn’t show the storage conditions for Material 2. This means you would have to look at the material that had the first load/change of the property. This is not desirable because I think the reviewer will be prone to error.

This review app does show all of the changes (material additions) but is not the best. It would be better to query out a table of the materials added with all fields shown. Maybe you have to clear the row after each addition or store in table a different way. If I could demonstrate an effective way to store and review materials then I could see how we could translate to samples, parameters, etc.

The way I like to think about building data models in general is to have a table for all physical artefacts. So a Table for Batches and a separate Table for Materials. What you are trying to do is have a table for the activities that happen to a batch, ie Material Additions which is where the confusion is.
If you have an artefact table like Batches and Materials then the history widget will show you the activities that happened to the physical artefact. If you added two materials to a batch then the Batch table will be linked to 2 rows in the materials table and you will have history records for all 3.
Here is an example diagram from a similar batch scenario
If you follow this principle and also add a trigger to save all app data (App. Save All Data) at the point the activity happens in the app, then you should have all the data available to show a complete contextualized batch history.