I have one table,
FRUIT, describing some measurements that I make:
ID Tag Amount Units 1 A 75 Apples 2 B 23 Bananas
I would like another table,
HISTORY, that tracks the amount of fruit over time, much like a historian. That is every x seconds/minutes I would like to record the current amount of fruit at that time.
Time A B earlier 35 45 Now Now 35 23 Now 75 23
The trick is that if the
FRUIT table is updated to include say Carrots one simply adds a record. How then would one add a field to
HISTORY to account for this?
My understanding is that the user has to specify the information for
FRUIT i.e. when adding a record to
FRUIT, this is simply an extra record. For
HISTORY however I'm now adding an extra field. Is it possible to do this on the fly? I know before hand that every column other then time is of type numeric/double/float and I can fully specify the other properties for the field using similarly known information. I'm just not sure how to do this automatically.
If it helps any I'm in MSAccess for now but will switch to MySQL soonish.