I am new to Grist and to this whole process. This is my first attempt in this arena, so please forgive any ignorance on my part.
I am trying to create a database of electronics repairs, as I am taking up a study of this field. My initial focus is on the device being repaired. I want to be able to add a device that is being worked on in a table of devices. I want each device record to contain “device type”, “brand”, “model”, and “model number.” I want to be able to keep connections between all of these fields so that I can later do data analysis on the relationships between these fields and the resulting repairs.
Currently, I have created a table labeled “Device Types” with one column that is a list of all device types. Then a “Brand” table that has two columns: one that is all of the brands, and the other column is a reference list of all device types that this brand creates. My third table is called “Models”, which will have 3 columns: the device type (a reference to “Device Types”), the brand (a reference to “Brands” with a dropdown condition of $device_type in choice.Device_Types), and then a specific model. However, this is where I am struggling to implement the desired behavior.
I want to be able to enter information that may not match what exists in the current tables, and it will dynamically update the tables to keep all relationships dynamically updated. So, for example, let’s say that I have the brand “Microsoft” with the current device types of “phone”, and “game console” connected to it. However As let’s say I am working on a Surface 3 tablet. So in the models table, I would enter device type of “tablet”, and then brand of “Microsoft”, and then a model of “Surface 3.” Since device type of “tablet” is not currently in the device types reference list for Microsoft in the Brands, I want that table to dynamically update, adding “tablet” to it’s device types.
My long-range goal is to then have a “Devices” table where each record is a specific device that is worked on. So, for instance, I may work on a dozen Nintendo Switches, each with their own records and their own repair records, but each would be related, by sharing references to the same Brand, device type, and model. (I’m also trying to find a way to tie in hardware revisions as well) I want to be able to add a device, starting with device type, which will then narrow down the field of brands, which will then narrow down the field of models, etc. However, if I intentionally add in details that don’t match the foundational tables, those tables would dynamically update to reflect the new relationships that didn’t previously exist. (I hope that all made sense. )
I am trying to find the best way to accomplish all of this. Do I need to create a Python expression that will do this? Is there maybe a better way to structure my data to accomplish my goals? Any assistance would help.