Arthur Fuller
fuller.artful at gmail.com
Wed May 16 09:45:34 CDT 2007
Thank you for your considered response to our numerous comments, although I did kind of hurl when you wrote that "I don't consider any one of them to be more right or wrong then another." (ibid) You have to get over that. Databases are not a morally or intellectually relative universe. Just ask JWC, for example. More seriously, there are only four people on this list to take seriously, and I am not one of them. Arthur On 5/16/07, Jim Dettman <jimdettman at verizon.net> wrote: > > 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. > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >