Charlotte Foust
cfoust at infostatsystems.com
Wed May 9 16:29:17 CDT 2007
>>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. <razz> Charlotte -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman Sent: Wednesday, May 09, 2007 2:26 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A database design for contacts.... ...my girl :) William Hindman ----- Original Message ----- From: "Charlotte Foust" <cfoust at infostatsystems.com> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Wednesday, May 09, 2007 5:00 PM Subject: Re: [AccessD] A database design for contacts.... >I agree and disagree, Arthur. In some databases, like membership > databases, there are categories of membership which include persons or > organizations. The organization may not provide a contact, but it still > has an address and phone number, so it still needs to be recorded, > particularly if it pays dues or makes contributions. Persons are all > contacts, but not all of them may be associated with an organization. > The same rationale holds true in a "family" database, except that all > families will have at least one person in them at some point in their > existence. > > You can address the problem in part by using a DBMember table that > contains a unique key for each organization or person + a membertype > value. The PK of that table would become the PK of the organization or > person table records. You would need a separate table to make the > connections between person and organization, and some way to indicate > whether a person is the primary contact for that organization. > Addresses, phone numbers, etc. would be linked to the DBMember and would > include a field to categorize them (home, mailing, office, etc.). > > Charlotte Foust > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller > Sent: Wednesday, May 09, 2007 1:32 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] A database design for contacts.... > > I've been down this particular road a couple of times, Jim. > > Your brief description masks many duplicate references (m:m tables), and > IMO confuses contacts with organizations. (Lately I have been busted > here for such opinions, but I plod on.) > > A contact is a Person, not an organization. An organization has one or > more contacts (if it has zero, why record it?). > > A physician may also be an attorney or a city counsellor (that's what we > call them in Canada, feel free to translate -- the point is this person > may have run for office as the rep for district xyz and either won or > lost, both of which facts may be important, depending on the app. > > Now: addresses. The simple fact of the matter is that most people who > run for office are affluent, and therefore the odds are that they own > multiple residences, one a home, another a cottage, another a shack in > Martinique -- whatever. No slam intended upon such fortunate people. I > want to concentrate instead upon the problem that Person X has three > addresses, and her domicile therein is dictated by the month of the > year. She will be in Toronto from May to July, then go to Martinique for > a few months, and ten to France just in time for the Cannes film > festival. > > Addresses, therefore, are scoped by time. No point mailing her a letter > to Montreal when it's time for the Cannes film festival, and so on. > > This could grow even further complicated by the fact that she sells the > cottage in Nantucket and purchases another in New Hampshire. > > In short, you have raised a non-trivial problem. As it happens, it is > one of my three favourite problems. I have spend a long time thinking > about it and have yet to discover a magic bullet. > > Arthur > > > On 5/9/07, Jim Dettman <jimdettman at verizon.net> wrote: >> >> 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 >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com