DB Design

[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

 

[William \(Bill\) Vaughn]

It sounds like you need a few hours/days with a consulting data access
architect. Many contributors to this newsgroup do that for a living (or as a
sideline) and yes, that is something that Beta V Corporation does.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[AMDRIT]

Bill,

I appreciate the offer but I will make do with what I have. I don't think
this question invades trade secrets and I should not have to pay someone
"smarter" or "more experienced" than I to say "Oh you are looking to create
an abstract factory."

When given a question, each of us begin to formulate the answer based on
knowledge and experience. I wouldn't know about Subject/Observer with out
someone taking the time to mention it, and then me browsing the web to get a
handle on it.

Someone somewhere encountered a situation that I described, then formulated
a pattern and then they or someone around them coined a term for the the
pattern. This is the tidbit that I am looking for. I can read and, for the
most part, I am self taught in everything computer.

While I may not be awarded the MVP, contributed in as many publications, or
speak Thai, I am accomplished in my own right. Not taking anything from
your incredible journey and experiences, I just want it to be known that I
was not looking for someone to do my homework.

If I wanted formal training on a subject, such as test driven development,
then I would look to hire someone to train my team and I. I don't think
modeling data at a 10K foot level counts as formal training.

I can appreciate that several of my last posts go unanswered, usually they
are long winded and more complex than the average post. Unfortunately,
there doesn't seem to be a community for folks at my level. I don't pretend
to have all the answers, nor have I encountered every type of issue out
there. I rely on my intuition, my experience, and my team to get the job
done. My team and I are pretty intelligent and resolve issues on our own or
via peer training.

So now, we will invent our own pattern and give it a name. We will create a
write up about it and post it on the code project. That is, ofcourse, we
are able to minimize impact on performance and development with the
solution.

I did not take your post as a personal afront, please do not read the same
into mine. I realize you have to put food on the table just as I do, I did
not take this as a cheap plug post, I am just not prepared to bring in more
talking heads on a project that is well manned.

 

[Earl]

Your issue relates somewhat to a design that I have created for a different
business process. I think you end up with a solution that is somewhere
between a data warehouse and an OLTP system. That is, you probably need to
implement what I call history tables in order to maintain state for
transactions that may later need rolled back. Your history tables would
allow you to also go as deep as you need in a rollback. Of course that will
also require an expansion of your database design. As to the issue of joins,
it is easy to make a mistake joining between "current state" records and
transactional records and I would not recommend doing so, but otherwise I
would not be too concerned about the number of joins, so long as the query
was otherwise efficient.

 

[Cor Ligthert [MVP]]

Amdrit,

Some simple questions:
How long did it take for you and your team to get to the design you have
now.
How many resources do you have (people you have interviewed by instance)
How many documents does your knowledge base contains that made this design?

As one of those extended half an hour, what do you than expect from us,
while if it is less................ I assume what I want to write then.

Cor

 

[Cor Ligthert [MVP]]

Typing correction

I assume that you know what I want to write then?

 

[Guest]

Hi AMDRIT,

It sounds as though you could benefit from adding a couple of fields to your
details table. A bit field indicating "In Force" would probably help the
most. You would want to restrict it so that only one record, per policy, per
detail type (premium change, coverage change, etc.) would have the field set
at any time. This would probably help with the rollback. I think I would also
consider a seperate field for "Detail Type" (premium change, coverage change,
etc.). This sould simplify your queries quite a bit. Finally a simple
"Sequence Number" field for each detail record of a given type might help
with the queries as well.

Hope this helps some.

Tom