Database design appoach confirmation

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?
Jaison PeterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anoo S PillaiCommented:
There is not a SINGLE right database design, your approach is right only.

Keeping separate LOAN and CLAIM table is perfect.

Related to "Remaining columns in claim type tables will be group as different tables along with loanid and claimid " :-

Before finalizing the above, think of your reporting requirement too. Sometimes a junction table with LoanID, CliamID with or without LoanType could be useful.  Or even keeping the LoanID and LoanType in Claims table too would be fine ( but that would make your design rigid).

Say for example, assume that most of the report that combine Claim and Loan data needs data from Claim and Loan table, as per the current design probably you have to link various ClaimTypes table to link the data. This could be a problem if no. of rows are high.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hi, a good design would be to create these as related tables but not parent - child relation. simply add a foreign key to both tables. It will allow you to traverse data both ways. If every claim must has a loan (or vice versa) you will create this as identifying relationship, otherwise it will be a non-identifying relationship.
Similarly put foreign keys in other tables with null allowed.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jaison Peter, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.