Using Redundant Tables

[rand605]

I am trying to properly design a table with the following structure:

tblLoan (Main Table)


LoanID
LoanDescription

tblApplicant (Primary Applicant)
ApplicantID
LoanID
FirstName
LastName
ISPrimary
.

tblApplicantCo (Co-Applicants)
ApplicantCoID
LoanID
FirstName
LastName

Business Rule

There can only be 1 Loan file
There can only be 1 Primary Applicant per Loan file
There can be multiple Co-Appicants per Loan File

After I thought about it some more the idea of a co-applicant table will not work. Each applicant also has a link to a income, expenses, credit table. Each of these tables are joined to the tblAppicant by the applicantid. But if I go the route of the tbApplicantco, then I will have to duplicate the income, expense and credit tables for the co-applicant. There has to be a better way to do it.

Any ideas would be great as I am now running around in circles.

Thank you in Advance
Art

[NTC]

the answer depends on better knowing your business; but it sounds to me that you are saying that income/expense/credit values have a 1:1 relationship to the Loan ID - therefore this data should be with the Main Applicant.

Because CoApplicant count can vary from 0 to X - it must be a separate table because it is a 1:Many - - - perhaps there is no other data needed in this table other than their contact info - don't know your business well enough...

seems to me you are on the right track

[rand605]

The Applicant table has a 1 to many relationship to the income, expenses, credit table because a applicant can have more than 1 source of income, different type of incomes, etc.

This is the same case for a co-applicant. Is there a way to maybe use the applicant table for the both the primary applicant and the co-applicant records?

[NTC]

yes you can surely put main & co in the same table.....just be sure there is a designation field (i.e. M vs C) as well as the common Loan ID field....so they will sort together correctly....