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:
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.