[AccessD] A database design for contacts....

>Lately I have been busted here for such opinions, but I plod on.

LOL, boy do I know that feeling.

>An organization has one or more contacts (if it has zero, why record it?)

Just about the only point I take exception to.  Often times things are
mailed to an organization, not to a specific person.  A phone bill, an
electric bill.  The phone company only wants to get the bill in the door, it
will find its way to the right place after that.

>In short, you have raised a non-trivial problem. As it happens, it is one
of my three favorite problems. I have spend a long time thinking about it
and have yet to discover a magic bullet.

Amen, and amen.

What are the other two favorite problems?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, May 09, 2007 4:32 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A database design for contacts....

I've been down this particular road a couple of times, Jim.

Your brief description masks many duplicate references (m:m tables), and IMO
confuses contacts with organizations. (Lately I have been busted here for
such opinions, but I plod on.)

A contact is a Person, not an organization. An organization has one or more
contacts (if it has zero, why record it?).

A physician may also be an attorney or a city counsellor (that's what we
call them in Canada, feel free to translate -- the point is this person may
have run for office as the rep for district xyz and either won or lost, both
of which facts may be important, depending on the app.

Now: addresses. The simple fact of the matter is that most people who run
for office are affluent, and therefore the odds are that they own multiple
residences, one a home, another a cottage, another a shack in Martinique --
whatever. No slam intended upon such fortunate people. I want to concentrate
instead upon the problem that Person X has three addresses, and her domicile
therein is dictated by the month of the year. She will be in Toronto from
May to July, then go to Martinique for a few months, and ten to France just
in time for the Cannes film festival.

Addresses, therefore, are scoped by time. No point mailing her a letter to
Montreal when it's time for the Cannes film festival, and so on.

This could grow even further complicated by the fact that she sells the
cottage in Nantucket and purchases another in New Hampshire.

In short, you have raised a non-trivial problem. As it happens, it is one of
my three favourite problems. I have spend a long time thinking about it and
have yet to discover a magic bullet.

Arthur


On 5/9/07, Jim Dettman <jimdettman at verizon.net> wrote:
>
> Hi All,
>
> In light of recent discussions on lookup tables, I'm curious on how 
> different people have attacked the contacts problem.  For example, 
> let's say I need to keep track of Doctors, Attorneys, Therapists, 
> Hospitals, Clinics, etc.  A contact could be an individual or a group 
> or company.  For example, I might have three doctors working as  a 
> group called Plank Road Medical.
> You need to keep track of both the individual doctors and their 
> contact info and the contact info for the group.  Also the fact that 
> they are all associated.
>
> Do you:
>
> A. Have a table for every possible type of contact?
>
> or
>
> B. Have one contact table which includes a contact "type".
>
>
> and the second question; how do you handle companies vs. individuals 
> and link the two?  Do you:
>
> A.  Have a separate table for companies.
>
> B.  Have a flag which indicates if the contact is a company or an 
> individual and self-join to your single table.
>
>
> I know the right answer, but I'm wondering what everyone feels is the 
> practical answer.  Given the recent thread on Lookups, this is 
> probably a good topic for discussion.
>
> Jim.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com