| Groups | Blog | Home |
dotnet ado.net : DB Design
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
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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "AMDRIT" <amdrit@hotmail.com> wrote in message news:ebgkOwWmHHA.2272@TK2MSFTNGP02.phx.gbl... > 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 >
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. [quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message
news:uAC9qzYmHHA.4852@TK2MSFTNGP03.phx.gbl... > 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" <amdrit@hotmail.com> wrote in message > news:ebgkOwWmHHA.2272@TK2MSFTNGP02.phx.gbl... >> 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
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. [quoted text, click to view] "AMDRIT" <amdrit@hotmail.com> wrote in message news:ebgkOwWmHHA.2272@TK2MSFTNGP02.phx.gbl... > 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 >
Typing correction
I assume that you know what I want to write then? "Cor Ligthert [MVP]" <notmyfirstname@planet.nl> schreef in bericht news:u5KsWuamHHA.3656@TK2MSFTNGP06.phx.gbl... [quoted text, click to view] > 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 > > > "AMDRIT" <amdrit@hotmail.com> schreef in bericht > news:u8HpXXZmHHA.1340@TK2MSFTNGP04.phx.gbl... >> 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. >> >> >> "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message >> news:uAC9qzYmHHA.4852@TK2MSFTNGP03.phx.gbl... >>> 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" <amdrit@hotmail.com> wrote in message >>> news:ebgkOwWmHHA.2272@TK2MSFTNGP02.phx.gbl... >>>> 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
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 "AMDRIT" <amdrit@hotmail.com> schreef in bericht news:u8HpXXZmHHA.1340@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] > 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. > > > "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message > news:uAC9qzYmHHA.4852@TK2MSFTNGP03.phx.gbl... >> 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" <amdrit@hotmail.com> wrote in message >> news:ebgkOwWmHHA.2272@TK2MSFTNGP02.phx.gbl... >>> 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
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 -- Tom Garth [quoted text, click to view] "AMDRIT" wrote:
> 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 > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |