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.Feature #1I 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.Feature #2I 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 ignored/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?
Full data history, current values can be rebuilt ignoring provisional or invalidated changes
design patterns;database design
This sounds like a use-case for event-sourcing.(The following is a bit simplified view)Event Sourcing works by storing events, instead of data, and then constructing the actual data bij re-running the events all the time.An event log could be like:teacher A created student Bobby Tablesteacher A assigned student Bobby Tables to class Mathsteacher H changed grade of Flunking Fred to A+ on quiz 3 of course MathOf course you'd do it more structured, but that's the essence.To accomplish what you want, you could add the following data to each event:status (accepted, provisional, declined)ip addressinitiator (user performing action)Maybe also mark the user who accepted/declined a provisional change.The advantage of this is that when you later find out a modification was incorrect, you can change its status to declined, and the rest will be determined correctly again.