Relationships on tables for different types of contacts

[JaimieRodgers]

It has been way too long since I have used Access and I am starting to develop one from scratch for my dad's lending company. My thoughts around organizing my tables were this.



I have three different types of contacts so far

1 Borrower
2 Lender
3 Investor

All three have basic information of a contact that are in one table, but then there are more fields needed depending on what type of contact the person/company is. I also have the same type of relationship where depending on what type of company it is I will need additional information stored.

1 Individual
2 Corporation
3 Limited Liability Company
4 Partnership

I am stuck on relationships. How do I set up the relationships so that if a person is a borrower that is a corporation it only needs to occupy the contact table, borrwer table, and corporation table and the others are ok to be blank for that contact? I appreciate any help with this one. I am usually quicker when I teach myself.

[NTC]

Well your question is general so you can lots of different opinions which can all be correct in their own way.

I would view Borrower/Lender/Investor to be a 'role' - and would only have 1 table; not separate tables by role. One role may require fields that the other role does not. One could simply ignore the invalid fields or actively dis-enable fields based on the role designation.

Again the Individual/Corp/etc is simply a selection of type I don't see that this is an issue.

When you do have multiple related records is when you have to have a related table. Let's say ABC Corp has 10 locations or 10 employees and you want to db all. Then these should be in separate 'child' tables that link to the 'parent' table.

Hope this helps.

[June7]

Agree with NTC. There are many unknowns about your model and approach will be impacted by those other factors.

Could the same person function in more than one role (Borrower, Lender, Investor)?

How are contacts related to companies?