I am currently writing out my tables for a new sports database I am putting together.
I have a question about how to set up the basic tables and fields regarding projecting and backtesting. What do I mean? Well since its a sports db I want to be able to project average results/times/scores per individual for upcoming events.
For this I thought a "Projected" table would be a good idea to keep these clear of real results and the like. I would make a dual primary key "date" and "event".
Would there be a way to create a "Projection ID" so that each projection has a projection date created, formula used and projections(Stats created), so that If I realize some projections are inaccurate and change the formula calculating them I could recalculate using a new formula and match against real results, old projections and new projections to see if new formula is an improvement. So that I could see each version of projections by date, formula and projections. I do want to ensure these stay entirely clear of real results.
Most of this will come from queries but when planning to test and re-test keeping results(projected) from results(real) is there any key considerations?