Re: Database design for generating report cards

[Rick James]

> 1. create schema for each day of year.

No. Have a table of tests, one row per test.

Think of the "database" and its "tables" as containing the raw data. Then do the computation, on the fly, in the reports. Averaging, say, 100 test results over a day or range of days, is trivial for the computer.

Furthermore, treat the table with test results as the "source of truth". That is, if someone contests a score, that is the one and only place where that value is stored. Change it if you must. But then rerun the "report" (a SELECT) to get the average, etc.