I am working on 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?