On 2009 Jan 4, at 22:00, Jason L DeLooze wrote:
Richard,
In your "John, Mary, Bobby, and Susie" example, how do you handle
the situation where Bobby gives you a new address? Do you change
his address record, which also changes John, Mary, and Susie's
address, or do you give him a new address record? And what happens
if the new address Bobby gives is also already in your database of
addresses - meaning either that other Contacts still live at
Bobby's new address (maybe he married someone at that address) or
have moved and didn't notify you. And what do you do with their
(now probably bad address) - change it to "nothing" or leave it be
- suggesting Bobby now lives with them? And if Bobby later gives
you another address ... what can you infer - divorce, the other
family moved before he arrived, one of these "former" residents
(his spouse) moved with him, etc?
What a nightmare dealing with all these possibilities? It is
possible to handle the various situations and update the
appropriate information - if you trust the source, but why do it if
there isn't a really good reason? What possible value is the
information in a portal that shows everybody associated with a
particular address, since you cannot know the validity of that
information?
Sometimes de-normalization is a good thing.
Having all the other people living at the same household show up on
Bobby's screen should serve to wave a flag when Bobby contacts the
clinic to say "I've moved.". This should trigger the question "Did
everybody else in the family move, too?", and the answers to that
question should be captured in the database.
Also, since I use a "join" table to keep track of the link between a
person and an address, the "join" table has a field for "address
type", which could be used to record the fact that Bobby is off at
college (or reform school, or whatever) but still thinks of the old
family address as "home". It's perfectly possible under this approach
for a single person to have multiple active addresses.
I use separate database relationships to keep track of familial
relationships. I don't simply assume that people who live at the same
address ARE related, nor that those who live at separate addresses
are not. I run on the principle of "once a parent, always a parent",
including adoptive and step-parents.
And I use the term "covivant" (from the Latin for "living together")
instead of "spouse", "partner", or any of the many variants thereon
to indicate a committed relationship. Unlike parenthood, this one is
subject to dissolution.
But that's just the way *I* choose to do it. Other people may have
different preferences. ANY possible approach poses the potential for
problems if the incoming information is incomplete. The ideal system
(which exists only in fiction) should neither overstate nor
understate what the system actually knows.
