Linking 2 One to Many tables

[KLP]

I have the following tables (for business loans):

Borrowers - Contains info on the borrower, address, financial info, etc.
Loan info - contains details on the loan, terms, collateral, guarantors, etc.
Principals - contains financial info on each principal
Guarantors - contains financial info on each guarantor

One borrower can have many loans; but one loan has only one borrower, so
those are linked in a 1-to-many relationship.

One loan can have many guarantors and one guarantor can guarantee many loans

Each borrower can have many principals and each principal can be involved in
many borrowers with many other principals, e.g., Bob and Sue can be
princpals of ABC Corp. while Bob and Jane can be prinipals of XYZ Corp, and
Sue and Jane can be principals of SJ Corp.

Principals and guarantors can be the same; although that is not always the
case.

Presently, The guarantor table is linked in a one-to-many relationship to
the loan info table; and the principal table is linked in a one-to-many
relationship with the borrower table. As a result, where one principal has
several companies and guarantees each loan, I am entering the same info
several times. My solution is to break the one-to-many links for the
guarantor and principal tables and create a seperate table called
principal/guarantor. In that table put 2 questions - is this person a
principal? and is this person a guarantor? Then enter the record ID number
for the borrower they are a principal of and the record ID for the loan they
guarantee. Then when I enter a new loan, and there are guarantors, I go to
the guantor/principal table and enter the loan ID # they guarantor and enter
the borrower ID# they are a principal of. Now the financial info is entered
once.

I'm thinking the ideal setup would be to select from a list the principals
of the borrower and in the loan info table select from a list the guarantors
of that loan; however, I don't know how to do that.

Is my proposed solution reasonable? I am not sure it is good form though.
I would appreciate any ideas.

Thank you,
Kelvin

 

[andrewbruce]

Hi Kelvin,

The problem you're coming across is a many-to-many relationship, which
as you've noticed needs an extra table in between, to allow for two
one-to-many relationships, which eliminates repetition. Usually after
some trial-and-error you'll find that such a table can be called after
some other entity in the business model, that makes logical sense.

For example, I was creating a school reports writing program on top of
Access. I had to find the link between Students and Classes. It turned
out that the most logical link between students and classes was, after
all, the students' reports. Hence, one student to many reports, one
class to many reports. Such a link isn't obvious when you consider the
concrete entities, of people and pieces of paper.

It sounds like you need to do some similar concept building, but don't
fall into the trap of making one centralised table for linking other
tables - tables shouldn't be the ones doing ifs and thens that you
describe - leave that to your code (if you get round to it).

If you might ever need many guarantors for one loan, and many loans for
one guarantor, you can forget any direct relationship. You need a third
table between these two tables, let's call it Guarantees. I'll
illustrate the three tables:

Loans
--------
ID
BorrowerID
Term (etc)

Guarantors
---------------
ID
CompanyID (etc)

Guarantees
----------------
ID
GuarantorID
LoanID

These tables are connected like this:
Loans ('one' on ID) -----<('many' on LoanID) Guarantees ('many' on
Guarantor ID)>------- ('one' on ID) Guarantors

So now you can have multiple guarantees allowing you to have more than
one guarantor per loan, and more than one loan per guarantor. Don't
worry about rows being filled up in the Guarantee table - that's what a
relational database does, and the space used by IDs is minimal.

You'll need to do a similar trick for Principals and Borrowers. You
should be relating all of your tables with IDs, i.e. put a CompanyID in
each table that concerns companies, then link each table to a separate
Companies table. The creation of such a table should solve your
problems with Principals (not sure what Principals are...)

As for selecting in lists, you need to learn to create queries. These
tie multiple tables together into a list of fields, and tend to look
like a database would before it were properly divided into tables with
one-to-many relationships, although queries are much better behaved
than list databases!

Once you've created Queries, you should create an interface to the
database with some Forms.

As you can imagine, all of this takes a lot of time, which is why
building databases costs so much money.

If you'd like any more help give me a shout at my website
http://andrewbruce.net

Cheers,

Andrew Bruce

 

[KLP]

Thanks Andrew for the insight. I am familiar with queries and forms. The
twist with the setup I described is in many cases the principal and guarantor
are the same. One person is a principal of several companies and is also the
guarantor on their loans, but that isn't always the case. I went back to my
Database Design for Mere Mortals book and here is the solution, I think.
Setup a principal/guarantor table -- prin_guar and link it to 2 other tables
-- borrow link and loan link. This way I can enter a person's info once,
then enter the borrower ID or loan ID # for the borrower's they are
principals and/or loans they guarantee. Thus there is no direct link to the
borrower info table or loan info table. I printed your response and havn't
had time to study it. The above was developed over lunch.

Thank you,
Kelvin

 

[andrewbruce]

I might bark up the wrong tree because I'm really not familiar with
loans terminology, but here goes.

How about 5 tables:

Loans
People (or Companies)
Principals (People providing a loan)
Guarantors (People guaranteeing a loan)
Borrowers (People taking out a loan)

They are laid out as follows

People
----------
ID (one on any of Principals', Borrowers', Guarantors' > PersonID)
Name
Address
Tel No.
Criminal activity
etc.

Loans
--------
ID (one on any of Principals', Borrowers', Guarantors' > LoanID)
Term
Address of this loan's Big Tony
etc.

Borrowers
--------------
ID
LoanID (Many on Loans > ID)
PersonID (Many on People > ID)

Guarantors
----------------
ID
LoanID (Many on Loans > ID)
PersonID (Many on People > ID)

Principals
--------------
ID
LoanID (Many on Loans > ID)
PersonID (Many on People > ID)

This seems to make a bit more sense than my original suggestion...

Andrew

 

[Gino]

Hi Kelvin,
I don't know if what I am proposing is pertinent because the loan domain is
out of my range of experience, but I hope it will help.

I was thinking, why not have a single table for "Entities" with all
pertinent details (name, address, tel. etc.) and then check marks to indicate
if the Entity is a Borrower or if it is a Guarantor or a Principal or both,
etc.

Then, when a Loan is established, you could choose one or more Guarantors
and/or Principals from a combo drop down or listbox (based on check mark
being true), and their Entity ID would by code be entered in the proper field
in the Loan Table:
(e.g. Loan 1234, GuarantorID: 6778, PrincipalID: 9990).

If more than one Guarantor and Principals are involved in the loan you could
either, predetermine the max no of Guarantors and Principals involved in a
typical loan, say 5 for example, and have a Guarantor1,2...5 etc. and
Principals 1,2,...5, fields in your Loan Table and then enter the relevant ID
in Guarantor1 and 2... etc.,
or simply concatenate the different IDs of the Entities acting as Guarantors
and/or Principals in their respective single (text) fields in the loan table
separating each ID by a predetermined character like "!" so that, when you
run your queries for a particular loan, you can extract all Guarantors and
Principals from their respective fields.
I hope this might give you some ideas.
Gino GinCan
___________________________________________________________

 

[KLP]

In loan terminology a principal is the key person of the company borrowing
the money. Usually they are the owners.

The entire database focus is on the borrower.

Here is what I did.

Borrower info - info on borrower
borrower ID
loan info id

Loan Info - details on loan
loan id

Prin-Guar info - info on principals and guarantors
Pringuar id
principal link id
guarantor link id

Principal link
principal link ID
borrower id - enter the borrower id for companies they are principals of

Guarantor link
Guarantor link ID
loan id - enter the loan ID for loans they guaranty.

I created a table where that info is entered and you can see the borrower
name when you enter the borrower ID and loan # when you enter the loan ID.
Now I enter the info once. However, it can be a bit tedious leaving the
borrower screen to find the principal/guarantor then enter the ID#'s. I have
tried linking that to the borrower screen by borrower # via a query. but it
will only show people who have already been entered as a principal or
guarantor.

Any thoughts on this? Thank you for the tip, it was very useful.

Kelvin