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:

  1. Employees get hired
  2. Employees leave the company
  3. Employees get changes in pay

These events arrive as tuples:

  1. 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:

  1. select count(*) from employees
  2. select count(*) from employees where pay > 10000
  3. 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?

  • 1
    Search for "slowly changing dimensions" and "temporal tables".
    – mustaccio
    Sep 6, 2022 at 15:26
  • @mustaccio many thanks -- greatly appreciated
    – user975917
    Sep 6, 2022 at 16:28
  • Hi, and welcome to dba.se! Temporal databases. Also, look at Datomic for an interesting take on how to do databases!
    – Vérace
    Sep 6, 2022 at 23:16
  • @Vérace many thanks, will do
    – user975917
    Sep 10, 2022 at 21:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.