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

I agree and disagree, Arthur.  In some databases, like membership
databases, there are categories of membership which include persons or
organizations.  The organization may not provide a contact, but it still
has an address and phone number, so it still needs to be recorded,
particularly if it pays dues or makes contributions.  Persons are all
contacts, but not all of them may be associated with an organization.
The same rationale holds true in a "family" database, except that all
families will have at least one person in them at some point in their
existence.  

You can address the problem in part by using a DBMember table that
contains a unique key for each organization or person + a membertype
value.  The PK of that table would become the PK of the organization or
person table records.  You would need a separate table to make the
connections between person and organization, and some way to indicate
whether a person is the primary contact for that organization.
Addresses, phone numbers, etc. would be linked to the DBMember and would
include a field to categorize them (home, mailing, office, etc.). 

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, May 09, 2007 1: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