Is it time for a change in our thinking. (1 Viewer)

[PNGBill]

I have about 17,000 images of works of art.

Simon

I guess in a few years that may not be such an issue

 

[dportas]

I would have thought that Normalisation had a lot to do with performance. Especially when you consider Saving Calculated Values.

You mean avoiding the recalculation of values by having those values pre-calculated in the database? It's not normalization/denormalization that makes the difference. What matters isn't whether those calculations are attributes in the logical schema but whether they are physically persisted in storage or in RAM so that the optimiser can take advantage of those pre-calculated values. Persisting the calculations in that way is (in general) possible whether the schema is normalized or not - e.g. through a function-based index or server-side cacheing.

Even when you do need calculated attributes in the schema, they don't necessarily create dependencies that compromise your normalized schema. e.g. the calculated line item price on an order table normally is not functionally dependent on the current price in the inventory table because the inventory price will change whereas the order price usually will not. So no risk of anomalies and no violation of any normalization principle is caused by storing a calculated item price on an order. Where calculated attributes do have dependencies on other data they can still be stored without loss of data integrity. You achieve that by using materialized views and/or constraints to guarantee consistency between a calculated attribute and the attributes that determine it (AKA "controlled denormalization").

 

[PNGBill]

e.g. the calculated line item price on an order table normally is not functionally dependent on the current price in the inventory table because the inventory price will change whereas the order price usually will not.

I believe 99% of businesses would want the UnitSalePrice, SaleQty to be saved regardless if the UnitSalePrice was taken directly from another table or calculated some way.
The result of SaleQty * UnitSalePrice is arguable (my vote is do not save).

Most argument relates to large databases that take a long time to recalculate, say, Monthly Sales Statistics and by saving SaleTotal, you only need to sum the 2,000,000 invoices rather then recalculate the 20,000,000 line items.

This argument becomes speed related rather then Normalisation.

Early databases never had this issue because we assumed Statistics were run overnight and available (spread all over the floor) in the morning.
Now we want statistics at the drop of a hat.

 

[RainLover]

What happens when I buy a carton of milk and a loaf of bread.

I do get an electronic invoice/receipt.

The sale is recorded in the Database.

Does the Database store a new calculated balance or does it do this at the end of a time period. If so then is this not classed a a violation of Normalisation.

Then again maybe my question is out of scope for this forum and MS Access.

 

[Pat Hartman]

I have worked with many applications (mostly large and not Access) that store calculated values. Accounting applications do it and it works fine because you "close" accounting periods. Once the period is closed, you can no longer modify or add transactions for it. At that point, you may as well store the period totals. It is also done in inventory applications under similar conditions. Periods are closed and on-hand quantities are stored. With inventory applications they also do periodic stock takes. Those records are stored and used as the starting point for running balances. So instead of going back to May 1st, 1956 to calculate the on hand quantity of paper cups, you find the most recent stock take and sum from there.

There is a theme though. Balances are only stored when updates to previous periods are disallowed. If that rule weren't enforced, every transaction would result in recalculating every intervening balance.

In all cases, the storing of calculated balances in a transactional system is a violation of normal forms. Yet, knowledgable designers do it for practical reasons in systems with high transaction volumes but only in strictly limited situations.

So the answer to your question is - not likely. The day's transactions would be summed after the close of business (period closed) at which point they could be saved.

 

[dportas]

What happens when I buy a carton of milk and a loaf of bread.

I do get an electronic invoice/receipt.

The sale is recorded in the Database.

Does the Database store a new calculated balance or does it do this at the end of a time period. If so then is this not classed a a violation of Normalisation.

Then again maybe my question is out of scope for this forum and MS Access.

That depends on the database schema. The fact that you are calculating and storing a balance at some point in time doesn't necessarily violate Boyce-Codd or 5th Normal Form for example. A relation is in BCNF if every non-trivial determinant is a superkey. A relation is in 5NF if all of its non-trivial join dependencies are implied by the keys of that relation. So the answer depends on the database schema and on what exactly you mean by "normalisation" (e.g. what normal form).

 

[spikepl]

#25

The day's transactions would be summed after the close of business (period closed) at which point they could be saved.

Maybbe. Or maybe not. A discount store reorders for overnight delivery of fresh supplies around 6 or 7 pm, so already at that time they have to have a pretty good idea. An airline does not do a stocktake of remaining available seats at the end of the business day. Neither does a cinema. Etc etc. The point is to think before deciding, and do what is required.

 

[RainLover]

Well, that is not what I was expecting.

I felt that there was a strong feeling against storing calculated values, but it appears that there is one rule that overrides all the others. And that is common sence.

I did use the word Period which was non specific. This could allow for very regular storing for large volumes and not so often for smaller businesses.

In Accounting I believe thing are done in batches. Once the batch is processed and balanced it can then be commited. The only way to change would be by reversal which would leave the appropiate audit trail.

Thanks for confirming that I was of the correct thinking.

My next question will be along the lines of complex reporting. But not just now.

 

[Pat Hartman]

There is a big difference between an accounting application that processes thousands of transactions a day where designers make decisions to deviate from third normal form (or more likely 4th or 5th) because it speeds up reporting and the typical calculated field request we get here. The people developing the accounting application know they are deviating and understand the ramifications and are careful to take precautions and implement business rules to prevent data anomolies. Beginning Access users have no clue regarding the ramifications of stored calculated values and since they are not experienced developers they do not understand how to protect themselves. That's why they are invariably instructed to NOT store calculated values.

Whether we consciously acknowledge it or not, we have a fiduciary responsibility to the clients who commission our work to protect their data. That means that unlike the Wall Street thieves we should take conservative actions whenever there is doubt.

 

[PNGBill]

Some databases use a temporary value rather then a calculated balance.
Stock take is an example.
You "Freeze" the stock on hand value, then when the stock take count is known, any difference becomes a Transaction (Sale/Purchase).
This means One field is used to hold the Stock on Hand value for comparison only.

Early databases were designed with limited data storage being a big issue. My first experience was with a Prime main frame (two linked) sharing a 256kb hdd plus of course tape backup.
This mean't you had to be careful when retaining infinite transaction history.
Our GL software, today, appears to be built for such a system as it Rolls Over each year and retains little transaction history.

Who wants to printout cartons of transaction data each night/week/month/year and store same, any more ?? We do not.

With the storage capacity and speed of today's pc's I would prefer a system that retained as much transaction history as possible.
This implies Calculated balances for Stock ON Hand etc are a natural event.
Our business is Paper Less.