Re: database integrity

"Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message news:8icam2-fuv.ln1_at_pluto.downsfam.net...
> mountain man wrote:
>
>> "Jan Hidders" <jan.hidders_at_REMOVETHIS.pandora.be> wrote in message
>> news:K%6je.95748$hi2.5651565_at_phobos.telenet-ops.be...
>>> Kenneth Downs wrote:
>>>>
>>>> One question on my mind is the change in rules over time. Do we say:
>>>>
>>>> 1. Data is valid if it adheres to all existing biz rules, OR:
>>>> 2. Data is valid if it adheres to rules that were in force when it was
>>>> inserted or updated.
>>>
>>> Very interesting question. I would say that it is the simplest to let
>>> the
>>> static database constraints be applicable for all the data, and if you
>>> want to restrict what can be added (or deleted or updated) then you use
>>> dynamic database constraints (or transition constraints, as they are
>>> also
>>> called) to specify that.
>>
>> 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.

>
> Here is my example. The column "VAT" is added to orders. In
> down-to-earth
> terms, we are going to add a column to a table. What do you assign as a
> value for existing orders?

NULL if it is prior to the effective (legislated) implementation date for the VAT. Other processes associated with VAT would need to be prefaced with DATE => implementation.

Pete Brown
Falls Creek
Oz
www.mountainman.com.au Received on Wed May 25 2005 - 08:42:00 CEST