Are there accepted best practices/database features to solve the general problem of being able to view/query the state of a system at any given point in time? To describe the problem, using the following canonical example:
Suppose you have a sequence of events that happen in time:
- Employees get hired
- Employees leave the company
- Employees get changes in pay
These events arrive as tuples:
- employee_id, first_name, start_name, pay, status = (hired|terminated|paychange)
If we want run descriptive statistics on the database that reflect the state of the organizations at a given point in time. Queries of the sort:
- select count(*) from employees
- select count(*) from employees where pay > 10000
- select median(pay) from employees
One way to solve this problem is to keep all the events in an employee_events table and then just build snapshot employee tables for each date you want to analyze by applying the event in chronological order up to the data in question.
Is there a standard / better approach to handle this design challenge?
