I have something like a snowflake schema that decomposes data that I get in spreadsheet form into the numeric values that go into one table and information about what was measured, on what subjects, what column of the spreadsheet it originaly occupied, how, when, by whom, etc. into a network of other tables joined by foreign keys.
I have a stored procedure that dynamically creates and runs a query with a bunch of joins that can reconstitute any of the original spreadsheets or simulate a full outer join on any user-selected combination of columns from those spreadsheets (joined on the data having been collected from the same test subjects).
But, often the input spreadsheets contain formulas. I don't want to treat these calculated values as static data because then if corrections are later made in the raw data they will not propagate and because new formulas will sometimes be added after the fact
Can anybody suggest a generic design pattern for storing formulas associated with certain type of data and running them on the output if requested to do so by the user? I had originally wanted to do everything in the DB layer but after seeing how limited MySQL's aggregate functions and stats/math functions are, I'll settle for passing the formulas to the application layer (PHP).
This is general question about database design, so I don't know how much detail is helpful. If my quetion is too vague, please let me know what additional information I should inlude and I will reformulate this question accordingly. Thanks.
