Path: utzoo!attcan!uunet!samsung!brutus.cs.uiuc.edu!wuarchive!udel!princeton!phoenix!englandr From: englandr@phoenix.Princeton.EDU (Scott Louis Englander) Newsgroups: comp.databases Subject: Re: Database structure problem (SUMMARY) Keywords: structure index dbase foxbase relation Message-ID: <15270@phoenix.Princeton.EDU> Date: 11 Apr 90 18:34:55 GMT Organization: Princeton University, NJ Lines: 187 A while back, i posted a query asking for advice on how to structure a list of constants for use in calculating consumption based on meter readings. I made the analogy to a fruit business, and got several replies on what to do, which i've attached below. Thanks to all who replied -- what you said was useful, although my analogy was not very representative, and i should have just explained the real situation (as boring as it is!). I'll do this here, before i describe the solution i've settled on: I have one table of inputs (mostly meter readings), and one table of calculations (mostly consumption for each meter). Meter readings are recorded for about 25 meters daily, and the consumption for each meter for each day is calculated (along with equipment efficiency, etc.). Meter constants, or multipliers, are used to calculate the consumption: consumption = constant * (previous reading - current reading) These change occasionally, and sometimes the consumption for a given period of time must be recalculated when calibration errors are discovered, or when meters were replaced, but the data entry people don't find out until later. To perform the calculations for any given day, i must look up the value of the constants in effect for that day for each meter. I was hoping to set up a "smart" structure for the constants table, so i wouldn't have to go "locating" around for each value -- pointers would do the work. Coburn (below) suggests such a scheme (for the fruit business), but i was not able to make it work in this situation. Here is the structure for the constants table i've settled on: Field Field Name Type Width Dec 1 name Character 10 2 date Date 8 3 value Numeric 11 5 The file is indexed on CTOD("12/31/2100")-date (that's Character To Date, for you non-dbase people). This way, the most recent value for each constant is first. Then, to get the value in effect for any given date, i use the following function: procedure ConstVal * lookup value of given constant for given date * Constants.dbf must be indexed on CTOD("12/31/2100")-date ; so most recent is first. parameters cname, thedate lastdb = alias() select &co go top locate for trim(name)=cname .and. date<=thedate fnd = found() temp = value select &lastdb if fnd return temp else alert stop 2 "Invalid constant name: "+cname+"!" return to master endif *end constval Although this is time-consuming, it seems to work ok. Any comments are appreciated. Below is a summary of replies to my original query. ==================================================================== Date: Wed, 21 Mar 90 13:45:32 +1000 From: ghm@ccadfa.cc.adfa.oz.au (Geoff Miller) In comp.databases you write: >I thought of having a single price table with these fields: >eff_date >fruit_name >price One option would be a separate table to record, for each fruit_name, the date of the last price change. You then have immediate access to the record in the price table. Alternatively, you could use one table for your current prices, and put the records into the historical price table as a part of the procedure for entering new prices. The first option requires an extra disc access, but make make the procedure of updating the prices simpler. I had a similar problem with a file registry system, where we have to record the movement history for each file (ie, for each movement record the date and the person taking the file). Fortunately this system was written using Prime Information, which like Pick allows for multivalued variable-length fields. One such field is used for the date, one for the person - what we do is insert the new data at the beginning, so that the current values are always the first ones. Unfortunately you don't have this option with dBase.... ----------------------------------- From: mwohl@engnet.ufl.edu Date: 21 Mar 90 08:13 EST I haven't used foxbase so this might not help at all, but Clipper has a command set softseek on/off which, if set on, will place the record pointer to the first occurence of the key greater than the value you are seeking, if the value you are seeking is not in the database. Thus all you would have to do is seek the value and if the value you sought was not found, skip back one. I used this quite often, and it seems like something the other vendors would pick up on, but I'm not sure that they have. I hope this helps. Mike ------------------------------- Date: Wed, 21 Mar 90 14:14 CST From: TODD@kuhub.cc.ukans.edu I suggest you approach the problem from a different angle. Instead of a table of prices showing basically the history of price changes (which your not interested in given the problem discription), use a INVENTORY database of items that contains only the most current price (updates replace the old price with the new price). Then use a second table, INVOICE or TRANSACT, that contains the items identification code *AND* price. This record will "remember" the price of the item when it was sold even if the INVENTORY database has been modified with a new price. Does this help? -------------------------------------------------------------- Date: Fri, 23 Mar 90 16:36 CST From: TODD@kuhub.cc.ukans.edu > Well, actally i do need to maintain a price history, for cases when the > prices last month are discovered to have been different, but not > until this month, whereupon we will want to update last month's costs... Uh, you lost me. Prices for items sold last month are recorded in the invoice database with the items. For items sold this month, all you care about is the current price. You have a price history of sorts (I would never use it as such) by analysing the invoice database. You can certainly calculate revenue by looking at the invoice database. And "costs"?? This wasn't mentioned in the original problem. If you care about costs, then create another field like price to keep track of the cost of the item. -------------------------------------------------------------- Date: Wed, 28 Mar 90 16:12:53 PST From: mailrus!uunet!infmx!coburn (Dave Coburn) I would suggest that you store the info in two tables, like shown: Price Table: price_id /* Unique ID number for this row */ fruit_name /* Obvious */ eff_date /* Same as yours */ price /* Cost of some known quantity, such as */ /* the case. */ Inventory Table: inv_id /* Unique ID number for this row */ fruit_name /* Same as before */ fruit_cost /* Price id from the Price Table */ quantity /* Quantity on hand */ This would give you a unique link between the two tables based on the price_id, and permit you to run simple SQL statements to count your fruit, find out what you have in stock and at what cost, etc. Note that this would permit you to have, say, two cases of apples @ 5.00 and another two @ 5.20; each would have it's own cost row. You would decrease quantity available as shipments go out, removing the row when quantity becomes zero (assuming you need no further record of it). Likewise, you can tell when the rows in the Price Table are no longer needed by checking for matches in the Inventory Table.fruit_cost row. Good luck. David Coburn ...{uunet,pyramid}!infmx!coburn ------------------------------- -- - Scott