[AccessD] (no subject)

All,
 
  First, sorry I didn't get back to this sooner and thank you all for your
comments and thoughts.
 
  Second, I'm not going to retch on anyone's ideas<g>.  Clearly this is a
complex problem and there are many shades of gray.   I intentionally left
the original message simplistic because I didn't what to imprint too much of
my thinking on the question, leaving it as wide open as possible.
 
  It was interesting to see the wide range of responses and I don't consider
anyone of them to be more right or wrong then another.  Each one of us made
choices when approaching a particular task and given the same task, anyone
of us might have done the same (or not<g>).
 
  FWIW, my original layout was the simple "flat file" approach, simply
because I could easily nest contacts as many levels as need.  It looked like
this:
 
tblContacts - Available system wide for all claims.
Contact ID - Long - PK
Date/Time Created - mm/dd/yyyy hh:mm:ss
Created By - FK to tblEmployees
Date/Time Modified - mm/dd/yyyy hh:mm:ss
Modified By - FK to tblEmployees
Company Address - y/n
Contact Type ID - Long - FK to tblLookups - Carrier, Adjuster, Employer,
Doctor, Attorney, Physical Therapist, etc.
Company Name - Text
Salutation ID - Long - FK to tblLookups
First Name - Text
Middle Initial - Text
Last Name - Text
Suffix - Text
Company/Group ID - Long - FK to tblContacts
Title - Text
Services/Specialty - Memo - 
Notes- Memo - General comments
Rating - Integer - 1- 10.
Date/Time Last Contacted - mm/dd/yyyy 
Phone - Work - (###) ###-####
Fax - Work - (###) ###-####
Ext (#####)
Phone - Home - (###) ###-####
Fax - Home - (###) ###-####
Phone - Alternate Number - (###) ###-####
e-mail - Text
Web Site URL - Text
  
 
   Of course there are a number of problems with this as well.  Obviously
the multiple phone numbers is a no-no.  However for many reasons, I don't
need more then those.   I also don't care if someone has more then one
address as I only need to keep track of their primary address.  Part of my
thinking on multiple addresses is that if I do track them, then by all
rights I should also be tracking when they are at each address or which one
is "current", all of which is way beyond what I'm attempting to achieve.
 
  What the goal is, is to simply record " a point of contact", which is a
single address and one of several possible phone numbers.  However I have
two types of contacts; individuals and companies (or groups).  So I am very
uncomfortable having everything in a single table.  Once again as with the
lookup table issue, I would be relying on the programming to abstract part
of the normalization process.  For example, if the Company Address flag is
set, the fields First Name, Last Name, etc would not be filled in, but
Company Name would.  
 
  That's not great, but would be workable.    Then I came to contact type.
For an individual, it might read "Doctor", but for a company, it would read
"Doctors Office", in which there might be one or more doctors.  So based on
the company flag, I would have to switch the lookup list.  Now I'd be doing
more then turning on and off controls, but actually having to fetch
different sets of data, which is too far over the line for me<g>.
 
  Net result is that I'm sitting with this layout at the moment:
 
tblContacts - One record per individual.
Contact ID - Long - PK
Date/Time Created - mm/dd/yyyy hh:mm:ss
Created By - FK to tblEmployees
Date/Time Modified - mm/dd/yyyy hh:mm:ss
Modified By - FK to tblEmployees
Contact Type ID - Long - FK to tblLookups - Adjuster, Doctor, Attorney,
Physical Therapist, etc.
Salutation ID - Long - FK to tblLookups
First Name - Text
Middle Initial - Text
Last Name - Text
Suffix - Text
Group ID - Long - FK to tblContactGroups
Title - Text
Services/Specialty - Memo - 
Notes- Memo - General comments
Rating - Integer - 1- 10.
Phone - Work - (###) ###-####
Ext (#####)
Fax - Work - (###) ###-####
Phone - Home - (###) ###-####
Fax - Home - (###) ###-####
Phone - Alternate Number (ie Cell) - (###) ###-####
e-mail - Text
 
tblContactGroups - One record per contact group.
Contact ID - Long - PK
Date/Time Created - mm/dd/yyyy hh:mm:ss
Created By - FK to tblEmployees
Date/Time Modified - mm/dd/yyyy hh:mm:ss
Modified By - FK to tblEmployees
Contact Type ID - Long - FK to tblLookups - Insurer, Doctor's Office,
Employer, etc.
Name - Text
Services/Specialty - Memo - 
Notes- Memo - General comments
Rating - Integer - 1- 10.
Date/Time Last Contacted - mm/dd/yyyy 
Phone - (###) ###-####
Fax - (###) ###-####
Phone - Alternate Number (ie. Backdoor number) - (###) ###-#### 
e-mail - Text
Web Site URL - Text
 
 
That still doesn't get me normalized by a long shot.   Besides the phone
numbers, etc I really should break out the relationship between individuals
and Groups as right now, I could have two Presidents.  But again I'm only
trying to keep contact information.  The Title field is just for reference
and will not be used in any meaningful way.  I really don't care about
someone's role within a group or company, just that they are associated with
it.
 
The only thing I don't like living with is the possibility of having Nulls
in a FK field (an individual with no group affiliation), so I may go ahead
and break it out anyway.  Of course by splitting groups/companies out, I've
now lost the ability for a group/company to be related to anything else
without adding a lot more tables, but for this app, I don't think it will be
an issue.  
 
I am going to go through everyone's comments again however and think about
this a little more, but I think this is it.  Of course I'm open to any
comments anyone would care to make as well.
 
Once again, thanks for the time and thoughts!
 
Jim.