I've been tasked with designing a system, including it's database to capture data which changes through time but shouldn't overwrite existing values. I've also ran into other people describing these kinds of databases in their work, referring to them as "temporal databases". Here's why I'm writing to the cayenne list about it. I've been thinking...say you started with a regular database model (a cayenne model or an existing db model) like you would on any project. The modeler could conceivably generate the whole thing: a pair of "valid_from" - "valid_to" fields in every table, code which would transform an update command into a relevant update/insert command pair and finally, code which would allow transparent access to the most current information stored in the database. The generated API would be almost identical to the one cayenne generates now for a "plain" database, the only exception being the possibility to define a timestamp (maybe at the context level) which would allow you to do the same (SELECT) queries, but effectively moving back in time to the desired moment.
Concievably, you could do something like this: DataContext dc = ...; dc.setPointInTime(beginningOfTheYear); // prints out the number of employees on 2006-01-01 System.out.println(dc.performQuery(new SelectQuery(Employee.class)).size()); dc.setPointInTime(now); // prints out the number of employees on 2006-08-25 System.out.println(dc.performQuery(new SelectQuery(Employee.class)).size()); Obviously, a database like this would grow possibly very fast so it might not be a suitable design where space is a major concern, but I can think of a lot of applications where this would be a fantastic thing to have out of the box. I'd appreciate anyone with an opinion on this to comment. TIA, t.n.a.
