Before starting the design of your summary table, please verify the following points still hold true:
- That the business need for this KPI to be real time is absolute.
- That the performance of the SQL for this KPI against the transaction tables is not acceptable or its impact is negative on other database usage.
Below are general design criteria for summary tables:
- The summary table must have "de normalized" columns to help avoid table searches/scans, thus increasing the performance of record identification process, for example:
- Adding Case Super, Case Owner and Case Type columns to a summary table might mean that the need to hit the Case header table to identify what case records are required is eliminated. The identification of the records is now carried out against the summary table.
- The summary table should also contain other keyed columns that facilitate the retrieval of other related data items if needed, this also allows the summary table to be future proofed to some degree.
- For example, a "CaseID" column on a summary table provides access to other case header data, but vitally the record set has already been identified in point 1 so joining to the case header table is as performant as possible.