On Mon, 13 Feb 2006 18:44:26 -0800, "Bill Adkins" <Bill
I am trying to create a contact database where both the primary contact and
the spouse can both be clients. They need to be listed as separate contacts
since they can both own products, but I also need to link to each other. I
prefer to do this in the same table but I worry about this creating two
listings for each HUSBAND/WIFE and WIFE/HUSBAND combination.
It depends on how thoroughly you want to do this. If each contact will
be linked to either zero or one, no more, other contacts, you can put
a SpouseID field in the table; leave it null for those curmudgeonly
bachelors. This is somewhat redundant as you must fill in Desi in
Luci's record, and fill in Luci into Desi's, or risk having several
episodes' worth of misunderstandings result!
Another way is to implement a "many to many self join" with a
Relations table: it would have records for FirstID, SecondID,
Relationship. Again, you need two records to get the symmetry, or else
to use a slightly fancier query joining the Relationships table twice,
once each direction.
Finally, you might consider a Households table (perhaps containing the
mailing address, likely to be the same for both) related one-to-many
to your Customers table. This would have the advantage of symmetry
(everybody, even bachelors, belongs to one and only one household) and
flexibility (husband, wife, live-in parents, adult offspring etc. can
all be added).
John W. Vinson[MVP]
