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

Jim

...ok, I'll bite :)

1st level
...tblentity
.......entityID (AN)
.......typeID
.......other fields unique to entity

2nd level
...tblorg
......entityID (pk/fk from tblentity)
......orgname
......dba
......notes
......etc

...tblpeople
......entityID (pk/fk from tblentity)
......honor
......fname
......mname
......lname
......suffix
......title

tbladdresses
......entityID (pk/fk from tblentity)
......addID
......fromdate
......thrudate
......notes

...the 3rd level provides joins for people/orgs with from thru dates, 
address breakouts for street addresses with mailing/shipping/billing 
checkboxes, c/s/z/c, electronic addresses, phones, etc all of which allow 
sharing of common address items, historical tracking, and so on ...been 
using this general template for going on four years now ...go ahead and 
retch on it, it works for me :)

William Hindman

----- Original Message ----- 
From: "Jim Dettman" <jimdettman at verizon.net>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Wednesday, May 09, 2007 3:37 PM
Subject: [AccessD] A database design for contacts....


> 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
>