Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
So if you reach to have a thousand fruits, you intend to have a table with a thousand columns? And then what, when you have a million fruits? – ypercubeᵀᴹ Oct 24 '13 at 16:38
Mostly I want a configurable historian. One would specify the properties in one table and automatically include the column for recording in the second. It's my understanding that databases are best for this. – Carel Oct 25 '13 at 13:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.