In a batch/transaction/detail file group there is a conflict between a structure that offers flexibility, and one that makes reporting easier and faster. How do you resolve that conflict?

In my case (a payroll program), different detail types follow different rules. For example, some are tax exempt, some accrue towards vacation, some are considered overtime, etc.

So far, I have not saved much historical detail but included "subtotal" fields in the transaction history file for each of the categories and report from those. Now I am being asked to break history down further and I am wondering if now is the time to follow "theory" and calculate all totals at report time.


This brings me to the question:

Assume that a detail file has Transaction Number, cost center, project code, income type, hours, and amount.

Is there quick and easy way (SQL Select statement) that will total all income types for each transaction. I think you have to create the records one by one with columns such as n01, n02, etc. and fill them the slow way (Scanning through detail file).

Perhaps I should consider a hybrid? I know it would not be third normal form then. How do you people resolve this problem.

TIA,

Alex