This is always an interesting question.

I am generally a fanatic about sticking to third normal form everywhere. That being said, some of DB design depends on the anticpated requests of the clients. For OLTP applications third normal is absolutely essential, but for datamart/datawarehousing stuff it's not always practical. Sometimes multiple joins on huge sets of data to return an aggregate just get too prohibitively slow...

One possible solution that I've used on occasion is to have separate tables which store aggregate values only and are updated via triggers from the tables that store the empirical data. Then queries that need to be run on the aggregates don't have to involve the base tables in the query. Also can help a little with the "Failed save" problem described below, since the trigger part of the transaction. It's a little safer than writing the aggregate value to the cursor prior to save. And if necessary, the table can be reconstructed usually using SQL with GROUP BY.

I don't usally do this in situations like the one you are describing, though, only cases where some complex reporting and analysis or OLAP tye stuff is required on large sets.

Again, though, depends on the requirements of the application in question.

Eric Shaneson
Cutting Edge Consulting

>I'd like to stick to my guns and stay normalized whenever possible. However, in the event of having to include the calculated field (for speed issues), I certainly like the idea of the recalc utility. I hadn't considered that before.
>
>>It's not generally a good idea to store any calculated data. The exception is when you need to be able to get at a value quickly that would take a long time to calculate on the fly but could easily be adjusted every time a value that affects the total is changed. This can be dangerous in a desktop database like VFP because it's actually possible for part of a trasaction to be completed if a user crashes while updating data. This means that the YTD field may not be accurate. You may want to add a utility to recalculate all of these values just in case.
>>
>>>When it comes to good normalization practice, is it better to not denormalize when attempting to store aggregate data? For example, what are the implications of deciding whether or not to have a "YTD purchased field" in a customer table? I've read that the decision to include this type of aggregate field is based on the need for speed, and therefore may necessitate including it in the customer table. I would like to hear some comments regarding this.
>>>
>>>FYI, thread #130522 was helpful with regards to some other normalization questions if anyone is interested.