I would like to create a website for a local school and then offer it to others. The school's main headache at the moment is capturing student marks and comments at the end of each school term. The student data on their current system can only be modified by the admin staff, who don't always have the most up-to-date information. This has in the past resulted in teachers being unable to enter marks for a student, either because their details are not there or are they not assigned to the correct class.
I would like to create a system where all details are provisionally editable by anyone, but the changes are visible only to the editor until they have been approved by the admin staff. Thus, teachers could insert missing students themselves and enter the marks without waiting for admin to fix it up first.
I am worried about students getting access to their teachers accounts and changing their own marks. I would like to be able to track all changes by time, login, IP address, etc so I could revert, for example, all changes made on Mrs Sweet's account (actually her niece) between 11PM and 12PM last Saturday night.
I am struggling to come up with a Database design that enables me to both keep a permanent record of changes made by users, mark any of those changes as
invalid, and be able to regenerate the latest data values from the change history skipping the excluded changes.
Is there already a design pattern / combination of patterns that will do the above? Are there hidden drawbacks? Has it been implemented elsewhere?