Database Field History Tracking via Nested VARRAY Storage

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database servers lack an efficient method to track and store historical values of fields, making it difficult to revert to previous values and manage data changes effectively.

Innovation Solution

Implementing a system where database servers automatically store and manage historical values using VARRAY structures, allowing users to specify fields of interest and maintaining multiple values for each field, enabling efficient tracking and reversal of changes.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of information

If database servers store historical values of fields, then data tracking capability is improved, but storage space consumption increases

Engineering Contradiction:

Improvehistorical data trackingVSAvoidstorage space

Core Design Contradiction:

Loss of informationVSQuantity of substance

Solution Approach 1:

The patent implements nested VARRAY structures where historical values are stored within the same table structure. Each field contains a VARRAY that can hold multiple historical values, creating a nested organization where historical data is embedded within the primary table structure rather than requiring separate storage tables. This nesting approach allows efficient storage of historical values while maintaining data organization and accessibility.

Inventive Principle:

Principle #7Nested doll (Nesting)

2Reliability

If database servers track modifications to field values, then data integrity is improved, but system complexity increases

Engineering Contradiction:

Improvedata integrityVSAvoidsystem complexity

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The patent implements automatic tracking of historical values through triggers and VARRAY structures that self-manage the storage and retrieval of field modification history. The system automatically captures prior values when fields are modified, maintaining audit trails without requiring manual intervention. This self-service mechanism enhances data integrity while minimizing the operational complexity for users.

Inventive Principle:

Principle #25Self-service

3Adaptability or versatility

If database servers store multiple values for each field, then ability to revert to previous values is improved, but access time increases

Engineering Contradiction:

Improvedata reversal capabilityVSAvoidaccess time

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The patent pre-allocates VARRAY structures within each table to store historical values, preparing the storage infrastructure in advance. When fields are modified, the system immediately captures and stores prior values in the pre-configured VARRAY structures using triggers. This preliminary preparation enables rapid retrieval of historical values when reversal is needed, as the storage structure is already in place and optimized for quick access.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS8224808B2Tracking modifications to values of various fields in a database server

Publication Date: 2012.07.17 ORACLE INT CORP

AI summary

Storing historical/previous values of a field of a relational database in the same table. The previous values can be conveniently used to revert back to any of such previously stored values. Thus, for each update/modify operation, the present value is stored as a previous value. In one embodiment, the previous values are stored in a VARRAY associated with each field. The indexed entries of the VARRAY store the present as well as previous values of the field.