Path: utzoo!attcan!uunet!samsung!usc!wuarchive!udel!princeton!phoenix!englandr
From: englandr@phoenix.Princeton.EDU (Scott Englander)
Newsgroups: comp.databases
Subject: Database structure problem (Fox/dBASE)
Keywords: dBase FoxBASE structure relation index
Message-ID: <14715@phoenix.Princeton.EDU>
Date: 20 Mar 90 21:41:49 GMT
Organization: Princeton University, NJ
Lines: 38

Here's a problem that many of you can no doubt help me with.  I have a
table of daily meter readings for several meters, and must somehow make
calculations based on those readings and constants for each meter (which
change occasionally).

For illustration, let's make an analogy to a fruit company.  The company
sells apples, oranges, and pears.  Quantities sold are recorded on a
daily basis, as are the total costs of each fruit sold.  Prices for each
fruit change occasionally, say once every 2 weeks or so, but not
according to a fixed schedule.  So how do i store the pricing
information in a way that i can calculate costs daily for each fruit?
Assume there are too many fruits to give each one its own price table.

I thought of having a single price table with these fields:

eff_date
fruit_name
price

where eff_date is the date when the price becomes effective, and
fruit_name corresponds to the name of the field in the quantities sold
table.  I may have to occasionally go back to old records, revise the
prices, and recalculate costs.  So i need to store a time history of
each price.  In calculating daily costs for each fruit, i'd like to
use a relation to point to the price table and find the most recent
price (i.e. for that date which is equal to or less than the date of the
current record).  The problem is that even if you could figure out some
way to index on a combination of date and name, the pointer would go to
the end of the file when the current date falls on a day when the price
did not change.

This seems like a problem that others would have encountered before.
Can anyone help?  (Please reply via e-mail, and i post a summary).


-- 

                                               - Scott