Concept of the version management in the Oracle tables

Many data stored in Oracle tables change over time: run informations, calibration parameters, slow control parameters, conditions in the analysis, ... All these data need a version management. Some data can have only one valid value at a certain date as for example the position of a certain cable, other ones as e.g. calibration parameters may even change over time for a certain DAQ run depending on the status of the analysis.

Two mayor requirements are:

• It must be possible to get a consistent set of informations at any date.
• To keep the history no information, even if it is wrong, should to be overwritten without trace, which means that only inserts should be made, no deletes or updates.

To fullfill these requirements, the following concept has been developped and tested:
Time dependend entries get a time stamp (date + time with the precision of one second) in form of three columns (Format: DATE):

valid_since	First date when the entry is valid.
valid_until	Last date when the entry is still valid.
invalid_since	Date when the entry is replaced by a correct entry (or better version in case of e.g. calibration parameters) and therefore gets invalid.

For run related parameters valid_since can be the run_start of one run and valid_until the run_stop of the same or an other run. Parameters as cabeling are changed between runs or even beamtimes. Valid_since is then the time when the cabeling was changed and the actual cabeling has the value 'year 4000' for valid_until. These two columns defined the validity time range.
All valid entries have invalid_since = year 4000. If an entry is corrected or a parameter set is replaced by a better parameter set (insert of a new entry in both cases) for the same validity time range, invalid_since of the old entry is set to the actual time on the server and the new entry has invalid_since = year 4000.
To garanty that there are no two valid entries at the same time these tables have a trigger (small compiled program in the database) which fieres every time an insert is made.

At a certain date t1 the entry is valid where t1 is between valid_since and valid_until and sysdate is smaller then invalid_since.
SQL> SELECT ... WHERE t1 BETWEEN valid_since AND valid_until AND SYSDATE < invalid_since;
This shows the advantage of the concept defining a validity time range. Each entry is independend of the other entries, there is no search needed as "give me the last entry before a given date" (SQL statement with a subquery which is quite time consuming).