Re: database integrity

mountain man wrote:

> "Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message
> news:q1cem2-am2.ln1_at_pluto.downsfam.net...

>> mountain man wrote:
>>
>>>>> Additionally, in more complex change, the retrospectively entered
>>>>> data may need to be addressed and essentially "converted" to conform
>>>>> to the implementation of new rules. This conversion will be a once
>>>>> off update of the data, to get it to conform to new rules.
>>>>>
>>>>
>>>> Can we tie this to some examples?
>>>
>>> A more complex example might be furnished from the column
>>> "NEXT_RENEWAL_DATE" (often called ANNUITY_DATE)
>>> for a patent application or registration in any given country,
>>> held in an IP management database for patent attorneys.
>>>
>>> In some schemas there is provision (as separate dates) for
>>> the first, second, third, etc annuity, because - dependent upon
>>> the country - there may many renewals due.
>>>
>>> Intellectual property legislation is country specific, every country
>>> essentially having its own patent "Act" or "Laws" etc. This
>>> legislation changes from time to time. IOW the rules governing
>>> the calculation of this date (NRD) may need to be altered from
>>> time to time (for future annuities).
>>>
>>> So therefore, if you were to be running such a system which
>>> has already pre-calculated the series of next renewal dates
>>> in respect of a particular patent, and the legislation changes,
>>> it is quite possible that some of the future dates would require
>>> change, to reflect the changed legislation.
>>>
>>> Sample example: a country pays patent annuities at years
>>> 3,4,5,6,7,8 through 17 on the anniversary of lodgement date.
>>> Thus a case lodged 1/1/2005 will require a renewal action on
>>> 1/2008, 1/1/2009, through 1/1/2022.
>>>
>>> However today (25/5/2005) a new patent law decrees that
>>> annuities will be due 5 years after lodgement and then every
>>> two and half years thereafter. It will probably have a savings
>>> provision such that existing renewals will be in force, but
>>> once renewed, the following renewal will THEN be for 2 years.
>>>
>>> In such examples, where the schema uses a fixed series of
>>> dates for the future range of NRD's of a patent, the data
>>> existent in that series may be made INVALID as a result
>>> of new rules. Yes, it was valid, but with effect from the
>>> new patent law, today, it is invalid, and a conversion of
>>> that data will be required.
>>>
>>>
>>>
>>> In summary, some data entered as valid today in projection
>>> of todays business rules, may not be valid tomorrow due to
>>> changes in the business rules, and will need to be converted.
>>>
>>> Especially data relating to future workflow.
>>
>> I would say at first glance that data should not be generated that far in
>> the future.  Consider this.  You know that the law can change, and
>> therefore the data _cannot_ _be_ _considered_ _valid_ _when_ _saved_,
>> therefore it should not be saved.  It is not that the data was valid and
>> then became invalid, it is that it was never valid in the first place.

Ah, the joys of temporal data. Let's recap the issues.

1. A patent as you've described carries a long-term obligation for annunities. We define "long-term" here as "spanning one or more significant software upgrade". IOW, "long-term" means "trouble for programmers".
2. During the period of obligation, the obligations themselves may change.
3. For convenience, we can project annunity obligations and save them in the database. We are not burdened with a belief that doing something for convenience is wrong, only that bad data is wrong.
4. Because of points 2 and 3, when the obligations change we now have invalid data in the database.

This can probably all be worked out by distinguishing between completed and project obligations. When a projection is run out, each row has the status of being a projection. At some point the obligation is converted into an actual transaction, some type of payment. It is now no longer a projection but a reality. When a change is implemented, you have the option to clear and regenerate all projections, or you could mark them as defunct (instead of deleting them) so that they remain on record for the purpose of comparison.

So it is not that the data was _invalid_ at the time it was committed, so much as the fact that we were smearing together projections and actuals.  

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)