[AccessD] A database design for contacts

Jim,

I did not consider contacts as the thing I was designing
for, I thought of it as names in general.

Having designed one that is being sold as part of a
package, I designed it as follows:

tblName - any kind of name

tblNameRelationship - Relates names to each other and
     how they are related. related to tblName 2 ways
     NameID and RelatedNameID

tblNameNametype - all the types that a name can be since
     name can be of more than one type. related to tblName
     and tlkpNameType

tblNameAddress - all addresses associated with a name
     linked to tblName

tblNameAddressType - all of the types associated with a
     specific address linked to tblNameAddreas and
     tlkpAddressType

tblNamePhones - all phone numbers associated with a name
     liked to tblName and tlkpPhoneType

tblNameEmail - all email addresses associated with a name
     liked to tblName and tlkpEmailType

tlkpNameType - a list of all the types of names used

tlkpAddressType - a list of all address types

tlkpPhoneType - a list of all phone types

tlkpEmailType - a list of all email types

The design above would allow you to associate a doctor
with a practice, a clinic, and a hospital. It is extremely
flexible.

At 04:32 PM 5/9/2007, you wrote:
>Date: Wed, 09 May 2007 15:37:15 -0400
>From: "Jim Dettman" <jimdettman at verizon.net>
>Subject: [AccessD] A database design for contacts....
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <002b01c79271$738d0550$8abea8c0 at XPS>
>Content-Type: text/plain;       charset="us-ascii"
>
>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.
>