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 

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 p.start <= now() and now() < p.end and g.start <= now() and now() < g.end