I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or private firm based on the insurer. If government is insurer, claim is made to government, if private firm is insurer its made to private.
Currently in the legacy application, claim is taken as parent and loan is taken as child. But i want just opposite.I want loan as parent and claim as child. There will be many claims for a loan, but single loan for a claim.
Current structure is claim id and loan id will be there in claim table.In case of different types of claims, there are different tables (claim type tables), which i need to eliminate. Means, suppose it is a government claim, it has another table with government insurance related columns , plus the claim related columns. Similarly for private claim, it has another table with private insurance related columns , plus the claim related columns. So the claim related columns appear in each type of claim related tables, here it is government and private which are common.
So my approach to design the database to make loan as parent and claim as child is as below:
Group all loan related columns to a table called loan
Group all claim related columns from different claim type tables to another table called claim
Remaining columns in claim type tables will be group as different tables along with loanid and claimid as forien key.
Is this approach is correct or is there any better approach?
Best Answer
From the comments added to the original question, I gather that the central problem with the legacy design centers around types and subtypes (often called classes and subclasses) of claims. This is a classic problem in relational design, Often, new database designers will have had some formal learning concerning many-to-many relationships, data normalization, and similar topics, but no formal learning regarding classes and subclasses.
The Info tab under the Subtypes tag has a brief summary of the problem. It mentions inheritance and object modeling in passing. I want to expand on this a little. The relational data model, in its original form, has no support for inheritance, although some DBMS products have extended the model with inheritance features.
Fortunately, the problem is well understood, and you can gain valuable insights from people who have studied the problem in depth. I particularly like Martin Fowler's treatment. He bridges the gap between how programmers think and how data architects think.
In your case, it appears from your description as though there are a bunch of claim subtype tables, but there is no generic claim table. I'm going to recommend one particular design for your evaluation. It's called "class table inheritance". And I'm going to also recommend a technique called "Shared Primary Key".
In class table inheritance, there will be one table for the generic data that applies to all claims, no matter which subclass they belong to. This table will have a claim ID, used to point to a row, like the usual table. The other columns in the table will contain the attributes that are common to claims regardless of what class they belong to.
The other tables contain data that pertain to particular classes (types) of claims. The ID column of these subclass tables deserves particular attention. It's not populated in the usual way, by having the DBMS assign a unique number to it. Instead it is an "inherited" copy of the ID column of the Class table. This column is both the primary key of the subclass table and a foreign key that references the appropriate row of the claims table.
When you want to deal with a particular class of claim, just join the claims table with the appropriate subclass table. The irrelevant claims will drop out of the join.
The reason I put "inherited" in quotes is that it requires some programming on your part to cause this inheritance to take place when a new claim is added. You have to add the row to the claim table, then obtain the automatically generated ID, then propagate it to the subclass table, before making the insert into the subclass table.
That's my recommendation, but it's your call.