A
AMDRIT
Got a puzzler for anyone wanting to take on a challenge.
I have some data, which represents something like an insurance policy. The
customer may call up and change the level of coverage at any time. These
changes may or may not produce a change in premium but may produce a change
in legal obligation for my company.
In the event that a customer places a claim on their policy, my company must
first verify that there was coverage on the policy for the date of the
claim.
What this boils down to is that I need to intellegently preserve
transactional state of the policy each time a change is made. I am looking
for an effiecient way to store this data in the database and relate to it in
my business logic.
Currently what I am doing is creating a parent table for each of my entities
that act as a place holder for each entity and then I create a details table
to house the data points of interest. I relate the data via a parent
transaction table and a child transaction details table. A record is
created
in the transaction detail table for each row in a detail table that is
inserted.
Given the insurance example:
Policy
PK PolicyID
Policy Detail
FK PolicyID
FK TransactionDetailID
Driver
PK DriverID
FK PolicyID
Driver Detail
FK DriverID
FK TransactionDetailID
Vehicle
PK VehicleID
FK PolicyID
Vehicle Detail
FK VehicleID
FK TransactionDetailID
Transaction
PK TransactionID
FK PolicyID
Transaction Detail
FK TransactionID
In this model there are three types of allowed changes:
-- A correction, which allows updates and are not transactional (highly
critical of data allowed to be modified.)
-- A transactional change in policy coverage, (clone business objects,
change desired data points, insert new row)
-- A reversal, which allows a transactional change to be reversed.
Reversals throw out the biggest challenge, because the transaction it
affected is no longer valid, so the one before it becomes the current
transaction again. Reversals have the affect of never happened, and will be
ignored when checking for coverage. Reverals will display in the
transaction
log (for Q/A purposes). The only time a reversal is not authorized is:
--The original issuance (That requires a cancellation of the policy)
While the underwriter can only create a change against the most relevant
transaction, reversals place an earlier transaction in that billet, then
once
a change is made, it too can be reversed again making a prior transaction
most relevant.
Some of the issues that I am having with this implementation are:
-- querying against the tables can be laborious with multiple joins, code
table lookups and so forth.
-- determining the most recent version of the policy is often convoluted for
an active policy.
--Transversing the transactional tree for a policy can get confusing. Not
all relevant data for a given entity was modified during the target
transaction.
--Batch processing is complex when using native SQL, and slow when using the
business API. Granted batch processing occurs at night, but computers need
sleep too. (Currently, activity reports are taking 1.5 hrs to process via
business API's and this is still a prototype system).
Does anyone have an alternate solution for storing this data?
I have been idlely browsing the web for possible solutions, I don't even
know how to phrase the question. "Versioned Data", "Transactional Data",
Transactional Auditing. (Append methodology, theory, concept). This concept
cannot be unique to me alone.
My counterpart mainframe group "stacks" the transactional history and
deletes the data when a reversal is requested, this is a manual process for
them. Our requirment was to preserve this historical change.
Thanks in advance
I have some data, which represents something like an insurance policy. The
customer may call up and change the level of coverage at any time. These
changes may or may not produce a change in premium but may produce a change
in legal obligation for my company.
In the event that a customer places a claim on their policy, my company must
first verify that there was coverage on the policy for the date of the
claim.
What this boils down to is that I need to intellegently preserve
transactional state of the policy each time a change is made. I am looking
for an effiecient way to store this data in the database and relate to it in
my business logic.
Currently what I am doing is creating a parent table for each of my entities
that act as a place holder for each entity and then I create a details table
to house the data points of interest. I relate the data via a parent
transaction table and a child transaction details table. A record is
created
in the transaction detail table for each row in a detail table that is
inserted.
Given the insurance example:
Policy
PK PolicyID
Policy Detail
FK PolicyID
FK TransactionDetailID
Driver
PK DriverID
FK PolicyID
Driver Detail
FK DriverID
FK TransactionDetailID
Vehicle
PK VehicleID
FK PolicyID
Vehicle Detail
FK VehicleID
FK TransactionDetailID
Transaction
PK TransactionID
FK PolicyID
Transaction Detail
FK TransactionID
In this model there are three types of allowed changes:
-- A correction, which allows updates and are not transactional (highly
critical of data allowed to be modified.)
-- A transactional change in policy coverage, (clone business objects,
change desired data points, insert new row)
-- A reversal, which allows a transactional change to be reversed.
Reversals throw out the biggest challenge, because the transaction it
affected is no longer valid, so the one before it becomes the current
transaction again. Reversals have the affect of never happened, and will be
ignored when checking for coverage. Reverals will display in the
transaction
log (for Q/A purposes). The only time a reversal is not authorized is:
--The original issuance (That requires a cancellation of the policy)
While the underwriter can only create a change against the most relevant
transaction, reversals place an earlier transaction in that billet, then
once
a change is made, it too can be reversed again making a prior transaction
most relevant.
Some of the issues that I am having with this implementation are:
-- querying against the tables can be laborious with multiple joins, code
table lookups and so forth.
-- determining the most recent version of the policy is often convoluted for
an active policy.
--Transversing the transactional tree for a policy can get confusing. Not
all relevant data for a given entity was modified during the target
transaction.
--Batch processing is complex when using native SQL, and slow when using the
business API. Granted batch processing occurs at night, but computers need
sleep too. (Currently, activity reports are taking 1.5 hrs to process via
business API's and this is still a prototype system).
Does anyone have an alternate solution for storing this data?
I have been idlely browsing the web for possible solutions, I don't even
know how to phrase the question. "Versioned Data", "Transactional Data",
Transactional Auditing. (Append methodology, theory, concept). This concept
cannot be unique to me alone.
My counterpart mainframe group "stacks" the transactional history and
deletes the data when a reversal is requested, this is a manual process for
them. Our requirment was to preserve this historical change.
Thanks in advance