Requirements: DB: New Functionality

["Leonard Mada" <discoleo@gmx.net>]

REQUIREMENTS: New DB-Functionality

Dear TC Members,

I will propose a new feature to extend the DB component. This requirement may expand the SQL language itself.


A.) Description
Extending the SQL INSERT statement
INSERT INTO KEY(existing_primary_key_value) ...

This extension will allow to insert new data record reusing the primary key of an existing record. The existing record receives a new value for the primary key and is kept inside the table. [There are important reasons why old records need to be kept in the database - e.g. in the field of medical data.]


B.) Rationale
A specific record inside a relational database may have links to multiple records inside another table. It may become necessary to update the data in the primary table, but also maintain (for various reasons) the old data.

Therefore, currently one can do one of the following:

1.) insert new record and update ALL foreign keys / links that refer to the original record in all the tables
- highly time/resource consuming
- depends entirely on written code (and great propensity for errors! - to miss something out)

2.) copy the old record as a new one;
and update the data in the old record with the new data, therefore maintaining the old key

This option is certainly better than option [1], but it still conceptuall ugly and certainly consumes more resources than the newly proposed functionality.

C.) Details of New Functionality

If one of the table has the following record:
[primary_key_val1] [various_fields] [...]

Than it should be possible to reuse specifically this key with the following statement:
INSERT INTO [table] KEY([existing_primary_key_val1]) VALUES(...)

- the statement shall write a new record with the given data
- the primary key of this record shall be set to value [existing_primary_key_val1]
- the old record shall receive a new value for its primary key
[so the new primary key value is allocated to the old record, not to the new one]


RETURN VALUE:
- the statement may return the value of the new key (which points now to the old record)
- it may return NULL if the operation does not succeed

D.) Additional Features: VERSION KEY
- one may define a field as type "VERSION KEY"
- if there is a field of type VERSION_KEY, than the newly inserted record shall have its version key incremented by 1 over the old record
- all records are now well versioned

E.) Utility
This feature is highly useful for data that needs historization or needs to be kept for various reasons, including legal reasons. A good example is in the field of Electronic Health Records (EHR), where there are good reasons to maintain all versions of some specific data - and where medical data may often change (e.g. age or weight will change continuously - but a lot of other data may depend on these values - just as an example: treatment dosage).

-----------

This feature goes beyond the ODF-features and is actually applicable to all SQL-servers. TC Members are encouraged to fill the feature request upstream if deemed useful (and I think it is very useful - as some may know I am currently active in the field of medical informatics, working with an EHR framework covering over 4 million electronic health records).

The provided example is a minimal one. [Everything inside the EHR can be viewed as fitting in this concept.]


Sincerely,

Leonard Mada