Table design for storing joint accounts

[John Delano]

Can anyone give me some advice on designing a table structure that can
represent joint accounts in a donor/donation database for a small college?

The database currently has over 50-60 tables and is far more complex than
this, but essentially, there is currently one Account table with fields like
FirstName, LastName, etc. and there is a Ledger table that records all the
individual gifts and is related to the Account table in a 1-M relationship.
For unmarried accounts, this works perfectly fine. Things seem to break down
when we attempt to use this structure for two accounts that are married (and
we do need to keep track of both husband and wife, because they may both be
alumni of our college). I want to be able to represent donations as coming
from either husband and wife as if they were from both accounts. In other
words, when I open the wife's account screen, I want to be able to see her
husband's donations as well as her own and vice-versa.

Things get complicated when you factor in the possibility of a spouse dying
and/or the couple getting a divorce and then remarrying. For example, to
which spouse are the gifts attributed after the divorce?

I'm currently handling this through a M-M recursive relationship on the
Account table. The problem is that this is pretty slow, and because of
difficult to enforce program logic, there can end up being multiple marriage
relationships assigned to the same person (albeit accidentally).

Am I even approaching this correctly? Would a separate 1-1 recursive
relationship work any better?

Any ideas/hints would be appreciated.

 

[Jeff Boyce]

John

I'm not sure what you mean by a "M-M recursive relationship".

Since you need a way to know who is connected (and how?) to whom, can you
set up a Family (or Related) table that holds the M-M?

Have you checked via Google.com or at the Microsoft templates websites for
other designs?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J. Goddard]

First off, you might not want to restrict your thinking to husband-wife;
what do you do about children who become alumni too?

The question is, perhaps - does an "account" apply to an individual or
to a household?

Maybe what you need is a table of donors, and another table of accounts;
the donor table would indicate which account an individual donor was
(currently) associated with. That will usually be one-to-one, but will
allow for many donors to one account.

Doing this will allow you to change the "account" an invidual donor is
associated with.

Since the ledger records individual gifts, it could record the donor,
and then use a query to find out the account.

That is a bit of an oversimplification - a donor could easily be
associated with more than one account in a fiscal year, meaning you
would have to track dates of account-donor changes. Tricky, but quite
do-able.

Clear as mud??

Just some ideas you can consider.

Hope this helps.

John