The situation is akin to the following:
Assume I am a store selling fruits, I would like to record the costs of each type of fruit. Assume the customer has specific tastes, and they can differenciate everything.
- A fruit can be an orange, apple, pear, or peach
- It could be n days fresh from the vendor
- The fruits come from different countries and have different taxes for export
- Fruits are also valued by weight
- Fruits could have handling requirements (Fridge,water sprigs)
- Fruits could also be valued just because of different origins
One idea is to do what is done in some industries, assign a unique product code for each variation: e.g. Orange5dayfreshAustralia200gfridgeSydney
This however, would be a HUUUUUGE long list of values, and should any costs change, it would be hell to search for Apples from Turkey that are 200 grams have now 20cents more export tax. Adding new variables would also massively mess up the whole table and require much recoding.
The other way is to assume a standard orange, peach, apple and pear and different weight(2 dimensional table). On other tables, prepare a list of cost savings/increase for any given deviation. (Law of probability should show me I have 80% "standard" fruits, and 20% niche fruits from funky places having funky costs)
E.g. a 1 day fresh orange compared to a standard 4 day fresh orange is 50cents more, regardless of weight.
(Yes, this means the primary table would need to have at least one variable that dont affect the others)
Final constrain: The data entry is done by hired hands, needs to be simple to enter in Excel. The programing can be difficult, but not the general interface.
Any suggestions?