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
- The global assortment table has a list of global vegetable varieties own by a seed company. eg:
-Variety ID = Unique -Crop = Tomato -Crop Segment = 200g Large Beef -Prod Segment = Indeterminate -Variety = Sylvianna (Label)
- The local assortment table only has a list of vegetable varieties that a local subsidiary of the company is selling and or developing
-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?
- I would like to show the Crop, Crop Segment & Product segment columns per that particular variety in the Global assortment in a local assortment view.
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.