views:

33

answers:

2

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?

A: 

If the information needs to be encoded in the product code, you can use subcodes to build a complete product code.

Type of fruit
AP - Apple
OR - Orange
PR - Pear
PC - Peach

You can use Country Codes for the country, and other subcodes for handling and origin.

EXAMPLE:

AP06BR145HROR

is a Brazilian apple, six days old, weighing 145 grams, with HR handling and OR origins.

Obviously you would need a lookup table and foreign keys for each of the code types.

Robert Harvey
I have thought of this, but there might be suddenly changes in number of variables, e.g. suddenly fruits that are seedless are cheaper, and that depends on where they are from. (Seedless apples from Aus cost 20cents more, Turkey 50 cents more, etc. Adding the new numbers for the new variables under a AP06BR145HRORSeedless would really be a huge task.
Michael
A: 

You seem to have twigged your problem - your data structure is wrong.

Your list is really a collection of attributes that apply to each "batch" of stock you purchase - except perhaps for "n days fresh" where the attribute is the difference between the attributes of date picked and date delivered.

So what you really want is a data table for each attribute type, and then a transcation data table where you add the attributes. The input is a form where the attributes are loopups.

You also need your pricing rules in a table, which you can use to calculate prices.

If your transaction table records both sales and purchases then you can filter on the attributes to report by any combination you like.

But the bit that interests me is how you work out how old the fruit is you are selling. You might know from the paperwork that you have purchased x oranges from Spain on one day and y oranges from Italy on another day, but in the box oranges are just oranges - so how do you tell which are which? Or are you planning to use a FIFO or LIFO scheme?

alastair harris
Ah, as I said, its just a situation simulation, can't really say what this multi-variate table is for in real practice.
Michael
Lets just assume I label each fruit carefully. Maybe with the long long tag name Robert suggested. My current plan is to have a data table for each attribute, one table is types of storage costs, one for export costs etc. All this looksup to the attributes of a certain fruit. I also forgot to mention the customer wants to know a variety of different fruits each time he comes, and its breakdown in costs. Most prob as you said, an input form, and some checkboxes to do lookup. Biggest issues lies in complexity of input of new data and how to store it.
Michael