I have a construction_events table in an Oracle database. Users enter construction events into the table via GIS software.
For example, a user can enter a construction project for 2019.
The event_status would be entered as proposed.
Starts out as legit:
The record is legitimate at the time that it is entered. The user proposes a project for 2019 which makes logical sense.
Becomes out-of-date:
However, as time passes, and we reach say...2020, logically the 2019 project should have been changed to complete, deferred, cancelled, etc..
Unfortunately though, that rarely happens. Users often fail to change the status of events (despite my reminders for them to check). This results in records where the year was in the past (2019), but the status suggests the event is for the future (proposed). This is logically impossible (an event can't be in the past --and-- simultaneously in the future). So, we have a problem.
Question:
Often in databases, we can prevent wrong data from being entered in the first place (check constraints, no nulls, triggers, etc.). However, in this case, the record was, in fact, correct at the time it was entered, so the aforementioned QC measures aren't applicable.
How can I manage this situation so that users are alerted that their records are out-of-date, as they become out of date?
Note: I'm not an I.T. guy or a developer. I'm just a public works data analyst. This might seem like seem like a silly question with an obvious answer, so feel free to provide negative feedback.