How do I easily keep records in a database linked together?

Question

I've got a requirement that I believe must occur very frequently around the world. I have two records that are linked together and whenever a change is made to them a new pair of records is to be created and retain the same link.

The requirement I'm working on has to do with the insurance industry which requires me to deactivate the current insurance policies and re-activate them in a new row in order to show the history of changes made to the insurance policies. When they are re-created they still need to be linked together.

An example of how this process is intended to work from the view of rows in a database:

Insurance Id, Insurance Type, Master Insurance Id, Status

1, Auto Insurance, null, Active

2, Wind Screen Insurance, 1, Active

Note in the above how the link between these policies is denoted by the Master Insurance Id of the second row pointing the the Insurance Id of the first row.

In the code I am writing I am processing each of the policies one at a time so after the first step I have the following:

1, Auto Insurance, null, Inactive

2, Wind Screen Insurance, 1, Active

3, Auto Insurance, null, Active

When I process the second policy I get the following:

1, Auto Insurance, null, Inactive

2, Wind Screen Insurance, 1, Inactive

3, Auto Insurance, null, Active

4, Wind Screen Insurance, 1, Active //needs to be 3 not 1

You'll notice that when I create the new Window Insurance that since we copy the old row we end up with the Master Id insurance pointing to the inactive row.

In order to get around this, I have to keep track of the master insurance id of the previous policy that was processed which has led to the following code:

int masterInsuranceId = -1;
foreach(Policy policy in policyList)
{
    //copy the old policy so the new policy has 
    //the same details as the old one
    Policy newPolicy = policyManager.Copy(policy);

    //if the new policy is not the master insurance store 
    //the master its new master insuance
    if(newPolicy.MasterInsuranceId.HasValue)
    {
       newPolicy.MasterInsuranceId = masterInsuranceId; 
    }

    //save the details of the new policy
    policyManager.SavePolicy(newPolicy);

    //record the master id so we can update the master id 
    //reference on the next policy
    if(newPolicy.MasterInsuranceId == null)
    {
        masterInsuranceId = newPolicy.Id;
    }
    else
    {
        masterInsuranceId = -1;
    }

    //inactivate the current policy
    policy.Status = Inactive;
    policyManager.UpdatePolicy(policy);

}

Does anyone know how this can be simplified? What is the best way to ensure two records will remain linked to each other even as a history of the changes is recorded for each change made to the record?

Answer 1

What sort of database schema are you using? Usually, this is where relationship should be stored and I think this should be hanlded at the data processing level rather than code.

Here's a very simplified recommendation

insurance (< insurance_id >, name, description)

insurance_item(< item_id >, < insurance_id >, name, description)

insurance_item_details(< item_id >, < policy_id >, when_changed)

insurance__policy has a 1 to many relationship with insurance_item. An insurance__item has a one to many relationship with insurance_item_details. Each row in insurance__item__details represents a change in policy.

This way, a SQL can quick quickly retrieve the latest two items

SELECT FROM insurance_item_details, insurance_item, insurance where
             insurance_item_details.item_id = insurance_item.item_id
             AND insurance_item.insurance_id = insurance.insurance_id
             ORDER BY when_changed
             LIMIT 1

Or you can even retrieve the history.

(The SQL has not been tried)

So the idea is you don't duplicate insurance_item -- you have another table to store the elements that would be changed, and slap a timestamp with it represent that change as a relationship.

I'm not a SQL guru (unfortnately), but all you need to do is to insert into the insurance_item_details table, instead of making copies. From the way it looks, making copies like in your original example seems to violate 2NF, I think.

Answer 2

IF you had a bad code design and you needed to make a change would you refactor? THen why would you not consider refactoring a bad database design? This is something that is more ealisy handled in the database through good design.

If you are working in the insurance industry which is data intensive and you are not strong in database design and query skills, I would suggest you make it a priority to become so.

Answer 3

Thanks everyone who has provided answers. Unfortunately, due to conditions at work I'm unable to implement database changes and am stuck trying to make due with a coding solution.

After spending some time over the weekend on this problem I have come up with a solution that I believe simplifies the code a bit even though it is still nowhere near perfect.

I extracted the functionality out into a new method and pass in the master policy that I want the new policy linked to.

Policy Convert(Policy policy, Policy masterPolicy)
{
    Policy newPolicy = policyManager.Copy(policy);

    //link the policy to it's master policy
    if(masterPolicy != null)
    {
     newPolicy.MasterPolicyId = masterPolicy.Id;
    }

    SavePolicy(newPolicy);

    //inactivate the current policy
    policy.Status = Inactive;
    policyManager.UpdatePolicy(policy);

    return newPolicy;
}

This allows me to then loop through all the policies and pass in the policy that needs to be linked as long as the policies are sorted in the correct order...which in my case is by start date and then by master policy id.

Policy newPolicy = null;
foreach(Policy policy in policyList)
{
    Policy masterPolicy = policy.MasterPolicyId.HasValue ? newPolicy : null;
    newPolicy = Convert(policy, masterPolicy);   

}

When all is said and done, it isn't all that much less code but I believe it is much more understandable and it allows individual policies to be converted.