Just looking for ideas and/or thoughts regarding the best approach of creating a static reporting table. The solution I am working on needs to account for human interpretation and subjectivity on calculated data. This can change depending on staff involved in the process, and/or any via oversight or context of values changes.
i.e. the final field values can change at any given time. A "Summary Report" is generated at any given time and the values (Records) at that time need to be retained, but the ability to create "new summary reports" based on different perspectives of the same data needs to exists. If that makes sense...
I am thinking that a generic ERD may be as follows; where a Join table is added, and that join table contains duplicate fields of the original data table
SummaryReport --< DataValues JOIN (Duplicated w Field/values) >-- OriginalDataFields
In this scenario is it acceptable to 'hard copy' the calculated values into the JOIN table?
if not, what other options are available to prevent "copied" data, but retain a time dependent record field values