Need some help to determine the best way to setup the data structure in the following scenario.
I have two tables: 1. Global assortment 2. Local assortment 3. Country
-Variety ID = Unique -Crop = Tomato -Crop Segment = 200g Large Beef -Prod Segment = Indeterminate -Variety = Sylvianna (Label)
-Country ID = South Africa -Variety ID
Questions 1. I initially had an Assortment ID for table two but realized that maybe a computed key between Country ID and Variety would be better as I believe this would restrict me from accidentally adding a duplicate record (i.e more than one record that have the same variety in the same country? WOULD THIS BE A CORRECT WAY TO ACHIEVE THIS?
How best do I set this up. I would assume there is only two options.
2.1 Either a virtual column pulling the relevant info based on the product ID 2.2 Adding Crop, Crop Segment & Product segment columns in the Local assortment table with a formula that pull the relevant info when the variety is selected. WHICH WOULD BE THE BETTER OPTION?
I WOULD LIKE TO BE ABLE TO ACHIEVE THE FOLLOWING EXAMPLE: -CHANGE THE CROP COLUMN VALUE IN THE LOCAL ASSORTMENT TABLE AND THIS WILL ALSO UPDATE THE GLOBAL ASSORTMENT TABLE.
Hope I am explaining the scenario correctly and what I would like to achieve.
The answer to your first question is: Yes, you are on right track; combination (i.e. computed) key is a good decision in this case.
Regarding your second question, as long as Variety ID is a Ref column referring to Global Assortment table, you can grab all columns from Global Assortment table and show them in Local Assortment table using Dereference method:
[Variety ID].[Any Column from Global Assortment]
To clarify further, your 2.1 option is a good one, meaning a Virtual Column that holds a Dereference formula.
Check this out for more details about Dereference:
https://help.appsheet.com/expressions/expression-types/dereference-expressions
Thanks Reza. I thought as much. Let say I use the virtual column as you suggestedโฆwill I be able to edit the value? eg: Be able to edit say the a value in the crop virtual column in the local assortment and it will also change the actual crop value in the global assortment table?
@Michael_Pinto No, in a one-to-many relationship you wonโt be able to edit any column on the one side from many side. If you want to edit any row that physically located in a table, you have to edit that table directly.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |