Calculated fields - what to do with

[sueviolet]

I was recently given a db which has fields in some of its tables designed to store calculated values (ie: avg_mass and % gravel) In fact, there is a "summary table" which consists only of calculated fields.

After reading through posts on calculated fields, I have learning that this is not a good idea. Should I then, just delete all the fields that were designed to store calculated values.?If I can't store calculated values in tables, would the best option to be to use queries.

Also, some background, my job is to complie and enter data into this database. The database will then be given to a consulant to use to create a managment plan. I think this is why calculated field and summary tables were designed- to make it easier for this consultant. Also, this data will not be updated alot - it is fairly static.

What do you think the best route for me is?

Thanks for your help

 

[cogent1]

Calculated fields are not normally stored, because any caculation derived from tables in the database can be done with queries. The tables should represent your data in its simplest non-repetitive form. You would store an item's price for example, and you would also store a specific client's discount, but it would be unnecessary to have a table showing the discounted prices for items by client, because you just have to join the items table and the client's table in a query and the discounted prices can be calculated easily.

To the user of the database, it makes no difference whether the data comes from a table or a query-the result is the same. From your point of view as designer though, running a query is more efficient. You have a smaller and faster database and you can apply changes easily and quickly. If one client's discount jumps from 5% to 7.5%, you make ONE change to your client table to reflect the change and re-run the query. If your client purchases 100 items from you those 100 price changes are immediately reflected in the query results. What would you have to do to change the 100 prices in a table?

You can present a much more polished and professional product to your consultants by designing queries and reports that accurately reflect the underlying data. A jumble of poorly organized and possibly out-of-date tables might not achieve quite the result you desire!

Anyway, good luck...

 

[sueviolet]

Thanks for your answer - I have taken your advice