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

...my girl :)

William Hindman

----- Original Message ----- 
From: "Charlotte Foust" <cfoust at infostatsystems.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Wednesday, May 09, 2007 5:00 PM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>