Introduction

The temporal model is a lot less scary than it sounds.

Usually in a database entities are represented by a row in a table, when this row is updated the old information is overwritten. The temporal model allows data to be referenced in time, it makes it possible to query the state of an entity at a given time.

For example, say you wanted to keep track of changes to products so when an order is placed you know the state of the product without having to duplicate data in the orders table. You can make the products temporal and use the time of the order to reference the state of the ordered products at that time rather than how they currently are, as would happen without using temporal data.

The temporal model could also be used for auditing changes to things like wiki pages. Any changes would be automatically logged without having to use a separate log table.

An "entity" would be any item in your design, for example, a product, a user, a gallery folder, anything that you would use as a Model can be considered an entity. The Temporal Model allows you to track changes to these entities over time by storing changes, each new set of changes is called a "revision". Because each revision is saved along with time (temporal) data it makes it possible to get the state of an entity at any given time.

This implementation of a temporal model stores revisions of entities in the same table as the original data, removing the need for duplicate "log" tables. While the implementation here is not the only way of implementing revisions, it is the most flexible given the limitations of the ORM and the fact that it must work in multiple database environments.

Why do this in the ORM and not just choose a database system that allows it?

Implementing the temporal functionality within the ORM allows the mechanics to be abstracted out of the database level, this allows the use of any database that the ORM supports with temporal models, providing greater flexibility. This model does not replace the ability to use a database that supports temporal information but provides an "out of the box" implementation.

Use

The table must be set up with a compound primary key of the entity ID, just a standard auto-incrementing number (for example), and the timestamps that represent the valid time span of the row.

class Model_MyTemporal extends Orm\Model_Temporal
{
	protected static $_primary_key = array('id', 'temporal_start', 'temporal_end');
	protected static $_temporal = array(
		'start_column' => 'temporal_start', //The name of the column that contains the time this row is valid from
		'end_column' => 'temporal_end', //The name of the column that contains the time this row is valid to
		'mysql_timestamp' => false, //If set to true will use MySQL timestamps rather than unix timestamps
	);
}

Please note that the primary key must be a compound key between the entity id and the temporal timestamps.

Any relations that are defined using temporal models should relate only on the id and not both id and timestamp.