Hi all,

I'm working on an addition to an existing system, and I have a design decision
I'm facing that could be done a few ways. I'll describe the picture and the
choices I am contemplating for handling it.

We're a property tax service company, dealing with property held by trusts.
We're now extending a similar service to encompass residential mortgages.
I'm going to simplify the tables involved to get to the point more quickly.

Current setup:

loan table:

  Id I Not Null Autoinc Nextvalue 1 Step 1, ;
	LOANACCT C(20) Not Null, ;
	DISPLAY_ACCT C(20) Not Null, ;
	CONTRACT C(10) Not Null, ;
	LOCATION C(3) Not Null, ;
	BRANCH C(5) Not Null, ;
	SETUP_DATE D Not Null, ;

&& and others specific to a given loan ...

Property table

'PROPERTY.DBF
 ID I NOT NULL AUTOINC NEXTVALUE 18803 STEP 1, ;
 ORDER_DATE D NOT NULL, ;
 LOAN_ID I NOT NULL)  && FK relates to loan

&& others specific to a given property

A property can have multiple loans on it. It might be a HELOC,
or a 2nd mortgage, etc.

Ownership table

   ID I NOT NULL AUTOINC NEXTVALUE 1 STEP 1 &&- PK 
   PROP_ID I NOT NULL, ;  && fk - PROPERTY
   LOAN_ID I NOT NULL, ;  && fk - LOAN
   PCNT N(7, 6) NOT NULL ;

Property is 1-n to ownership
Loans are 1-n with property.

When viewing a property detail, it is easy to show the applicable loans.
I have that set in a grid on the form, no problem.

This is all okay, but I have a new requirement for showing 'related loans'
when viewing a loan detail, without respect to a particular property.
If any property under that loan has multiple loans, then these loans are
considered related and should be shown, again probably in a grid on the
data entry for for loans.

I'm considering a 'template ownership' table at the loan level,
with a master-secondary relationship like:


Template Ownership

   ID I NOT NULL AUTOINC NEXTVALUE 1 STEP 1 &&- PK 
   master_ID I NOT NULL, ;  && fk - loan
   LOAN_ID I NOT NULL, ;  && fk - LOAN
   PCNT N(7, 6) NOT NULL ;

The 'Master' loan would be somewhat arbitrary, but would then be used
to tie related loans to each other without reference to a specific property.
New property set up under an existing loan could have its ownership records
pre-populated from this template table. I see a potential synchronization
problem with this, though.

My other idea was to place a self-referencing FK in the loan table to it's 'master'
loan record, but I am not liking that either.

Suggestions?