3

I'm designing a database for my company to manage business loans. Each loan can have guarantors, which can be individuals or companies, that serve as financial backing in case the borrowing business fails.

I have 3 tables in concern: Loan, Person, and Company, which store information for the obvious. The dilemma I have is how to define the relationships between a Loan and a Person or Company to know the guarantors for each loan. There are three ways I can see to do this:

1. Create a single table, Guarantor, for all individual or company guarantors:

Guarantor:
 pkGuarantorID (int, primarykey)
 fkLoanID (foreign key mapping to the primary key of a row in Loan)
 fkPersonID (foreign key mapping to the primary key of a row in Person)
 fkCompanyID (foreign key mapping to the primary key of a row in Company)

The problem with this approach is that one of the foreign keys would always be blank since a Guarantor can only be a Person or Company, not both.

2. Create two new tables, Loan_Person and Loan_Company, representing the two different kinds of guarantors:

Loan_Person:
 pkLoan_PersonID (primary key)
 fkLoanID (foreign key mapping to the primary key of a row in Loan)
 fkPersonID (foreign key mapping to the primary key of a row in Person)

Loan_Company:
 pkLoan_CompanyID (primary key)
 fkLoanID (foreign key mapping to the primary key of a row in Loan)
 fkCmpanyID (foreign key mapping to the primary key of a row in Company)

Though clearly more normalized and likely a better option, this would take a little more logic to SELECT and properly combine or display the results.

3. Create a single table that references either Person OR Company:

Guarantor:
 pkGuarantorID (primary key)
 GuarantorType (signifies either Individual or Company)
 fkGuarantorKey (foreign key mapping to a primary key in Person if GuarantorType is Individual, or mapping to a primary key in Company if GuarantorType is Company)

This also seems like a good option, but would require the extra step of checking the value of GuarantorType before doing any JOINs.

Does anyone have any advice on which method to pursue? I was hoping to hear from people who have had similar situations so I know what headaches may be created or avoided in the future.

Thank you very much for taking the time to look at this!

EDIT: Anyone with similar questions, in addition to the link from @RBarryYoung, may also find these question useful:

Community
  • 1
  • 1
Brett
  • 1,267
  • 1
  • 13
  • 21
  • You might consider upvoting the helpful answers. – RBarryYoung Jan 04 '13 at 23:52
  • I was planning on it since both of your answers were helpful, however, I am new to the site and don't have the "reputation" quite yet. :) – Brett Jan 07 '13 at 16:41
  • Sorry, I thought that you could always upvote answers to your own questions...? If you can see the uparrow buttons over our answers, just try clicking them. If not, that's OK too... – RBarryYoung Jan 07 '13 at 17:10

2 Answers2

2

In my opinion there is no obvious best or worst answer, each has its pros and cons. Here are a few points to consider:

Solution 1 - Data integrity can be guaranteed using a CHECK constraint to force exactly one FK value to be populated.

Solution 2 - As you say, great for storing data, not so great for querying data. Which of these is more important to your app?

Solution 3 - This will work, but you will not be able to define an FK constraint on fkGuarantorKey, which will cause data integrity problems down the line. If I had to choose a "worst" solution, this would be it.

Solution 4 - You could also consider merging the Person and Company tables into one LegalEntity table, and have child tables for the person-only and company-only data. Your Guarantor table would then reduce to a simple many-to-many link table with two FKs. This is a good solution if many parts of your product have to deal with either people or companies in the same way. However, if your product always treats people and companies differently, then it's less practical.

Christian Hayter
  • 30,581
  • 6
  • 72
  • 99
  • Thank you for your response and additional thoughts - I hadn't even thought of the missing constraint on item 3. Between this and the link that @RBarryYoung posted below, I have found more than enough resources to further guide me through this! – Brett Jan 04 '13 at 15:37
2

This is what is known as either a "Category Relationship" or (more commonly today) "Multiple Table Inheritance". There are at least three different, viable ways to implement one depending on the constraints. This article here: http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server does a pretty good job of explaining how to implement them.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Great article - this helped tremendously and pointed me to a number of other resources and articles (many of which are on stackoverflow, however, I was not using the proper terminology when trying to search for existing answers). Thanks so much! – Brett Jan 04 '13 at 15:39
  • You should summarize the article in case of [link rot](http://en.wikipedia.org/wiki/Link_rot) over time. – Patrick James McDougle Apr 16 '14 at 20:47