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

Jim,

    Apparently, the objective is to extract different profiles of output / report, using the available data of contacts. A given contact might feature in more than one profile, without involving any duplication of stored data.

    For instance, profile named "Visit_UK", would not only include the particulars of contacts in UK (friends/relatives/hotels etc), but also those of travel agents / airlines etc in the home country. On the other hand, profile named "Visit_Australia" would include similar information pertaining to Australia & home country. 

    In both the above profiles, many of the items pertaining to home country would be common. However, the list delivered by each profile is complete and self contained.

    Use of an ancillary table having fields ContactID (as FK), and Link (holding the profile name) is found convenient. Data entry to this table takes place via a subform on the main form used for entering contacts data. Drop down list showing all existing profile names facilitates entries in the subform.

    Printing the final report for a given profile becomes very simple, as the only criteria needed is profile name. It over-rides all existing classifications and pulls the required information into one consolidated list.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Jim Dettman 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, May 10, 2007 01:07
  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.
  --