Calculating vs Storing Data?

[ions]

Dear Access Expert.

I have a choice of either calculating a field or storing it.

Specifically, I have two pieces of data ... departure time and arrival time which I have taken from another source. This other source also has the difference between these times. Currently, I am not storing the difference in a field as I have opted to calculate it in my database.

However, I am thinking that maybe it would be better to store it vs. calculating it because I have 35, 000 records and storage space is not going to be an issue.

What do you suggest if I my main concern is speed and effeciency?

Thank you very much.

 

[Rabbie]

The purist say "Never store calculated fields". If there is a chance that the values in the fields used for the calculation can change then they are correct because you could get inconsistent data. If the data is definitely fixed then there could be a case for storing it. If you need the duration then you can always calculate it in a query so in this case I would not store the field in my tables.

The choice is yours at the final crunch

 

[The_Doc_Man]

Given the tuple: <start date/time, end date/time, duration>, you have 3 items but only need two of them in order to compute the third one.

So the "store it" question is this: Which two will you use most often? Store them and compute the other when you need it.

 

[statsman]

Do you have control over the depature/arrival times?

If you are dealing with an airline (for example) you have no control as they may change their times without notice and your calculated fields are incorrect. You would have to go in and change every one.

If you do have control over these times, you could create a query that could update your calculated fields to the new times.

As stated earlier, it's not a good general idea to store calculated fields in a table, but if the latter case is true, it would be less risky.
If you have no control, you are in for a long and unpleasant journey.

 

[Simon_MT]

I would only store a "calculation" if it involved values from child records. This is purely for speed and ease like an invoice storing totals from the invoice details.

Simon

 

[ions]

the departure and arrival times will never change..... I have the choice of also importing the difference between them but I have chosen to calculate it instead. So I am wondering what you would do in my situation? I think it's better to store them.... the time calculation is quite complex no?

thanks

 

[Simon_MT]

I would only store the "calculation", in this case, only if there was a performance issue and the best way to test this is to try it out. Personally, I have found environmental or structural issues have a far greater effect upon performance. The databases we use "foundation" queries as building blocks and there a a number of quite complex Expressions in some of these. Our record numbers are disimiliar and we don't have any major performance issues.

Simon