Here is my favorite approach:
- Each table has a corresponding history table
- Write stored procedures (or triggers) to make sure that all actions are logged to the history tables
- On insert, add a row to the history table with start = now() and end = 31.12.2999
- On update, first update the most recent history record to end = now(). Then insert a new row with start = now() and end = 31.12.2999
- On delete, update the most recent history record to end = now().
Now you can write a point-in-time query, even with joins:
select g.groupname, p.productname, p.price from products_hist p, product_groups_hist g where p.id = g.id and now() between p.start and p.end and now() between g.start and g.end