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

Have a look at this article and mdb on managing tables with human 
relationships
especially groups of peoples  by Allen Browne.

http://www.allenbrowne.com/AppHuman.html

With Access SQL it does breakdown if carried too far because
Access SQL unlike SQL Sever can't easily handle nested SQL Trees.


Jim Dettman 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.
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada