Table structure and relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello - I;m trying to create a version 2 of my database that's normalized.
Have the following problem(s):

Am creating different tables for Contacts, PhoneNum, Addresses
Contacts can be persons _or_ organizations.
Relationship between contact and addresses is many:many. (I think I have
that working OK.) A person can have a home or a work address or both.
An organization by definition would have only a work address.

How can I refer or create a relationship between Person X and Organization
7? That is, how can I end up with a real-world mailing label, namely:

Person X
Title if any
Company 99
Address info . . . .

Right now table structure is
tblcontacts
prefix, firstname, midname,lastname, suffix, organizationName
tblAddresses - the usual suspects
tblPhoneNumbers

frmContacts beforeUpdate Event = "Last Name or Organization Name must be
filled in!" using a macro.
And I guess I want to prevent entry in Both LName & Org.

I have tried having a separate CompanyTable, but had problems getting all
addresses into the addresses/ContactAddresses tables, because I set up one
junction table between the two 'entity' tables and the Address table.

How do I avoid the following, or should I?
tblPeople
tblOrganizations
tblPEopleAddresses
tblORgAddresses
tblAddresses

Thanks!!! - Lisa
 
LReber said:
Hello - I;m trying to create a version 2 of my database that's normalized.
Have the following problem(s):

Am creating different tables for Contacts, PhoneNum, Addresses
Contacts can be persons _or_ organizations.
Relationship between contact and addresses is many:many. (I think I have
that working OK.) A person can have a home or a work address or both.
An organization by definition would have only a work address.

How can I refer or create a relationship between Person X and Organization
7? That is, how can I end up with a real-world mailing label, namely:

Person X
Title if any
Company 99
Address info . . . .

Right now table structure is
tblcontacts
prefix, firstname, midname,lastname, suffix, organizationName
tblAddresses - the usual suspects
tblPhoneNumbers

frmContacts beforeUpdate Event = "Last Name or Organization Name must be
filled in!" using a macro.
And I guess I want to prevent entry in Both LName & Org.

I have tried having a separate CompanyTable, but had problems getting all
addresses into the addresses/ContactAddresses tables, because I set up one
junction table between the two 'entity' tables and the Address table.

How do I avoid the following, or should I?
tblPeople
tblOrganizations
tblPEopleAddresses
tblORgAddresses
tblAddresses

Thanks!!! - Lisa
Click to expand...

I would recommend something like your last paragraph suggests but with just
a tblOrganization and tblPeople.

tblOrganizations would be your main table with a primary key of
OrganizationID.

tblPeople would have a primary key of PeopleID and a foreign key of
OrganizationID. One organization could have many people but it is unlikely
that one person would belong to many organizations (if so, needs rethink).
The person would have a work phone, home phone, mobile phone, each a separate
entity. The person could also have a work address that is an expansion upon
the Organization address (Building 22, Department ABC) and a home address but
each a separate entity.

An organization can have more than one address: General Motors headquarters
and a manufacturing plant in California. One solution is to include a
LocationType and Parent field in tblOrganization. For simplicity, look at
LocationType as Headquarters or Branch. The GM headquarters would have
headquarters in the LocationType field and an empty Parent field. The
California plant would have Branch in the LocationType field and the
OrganizationID of the parent in the Parent field. The California plant will
have a different OrganizationID than the headquarters but will be related to
the headquarters through the Parent field.

Good luck,
Roxie Aho
roxiea at usinternet.com
 
Hi Roxie - my comments are below . . .
I would recommend something like your last paragraph suggests but with just
a tblOrganization and tblPeople.
Click to expand...

I was hoping to have just one address table to eliminate possible duplicates.
tblOrganizations would be your main table with a primary key of
OrganizationID.
Click to expand...

Right now our main table is contacts - we have a total of about 4400
entries, maybe 3% of which are organizations. Everything else is driven by
contactID (payments, event registrations, etc.) so I don't think we can make
that big a change.
tblPeople would have a primary key of PeopleID and a foreign key of
OrganizationID. One organization could have many people but it is unlikely
that one person would belong to many organizations (if so, needs rethink).
The person would have a work phone, home phone, mobile phone, each a separate
entity. The person could also have a work address that is an expansion upon
the Organization address (Building 22, Department ABC) and a home address but
each a separate entity.

An organization can have more than one address: General Motors headquarters
and a manufacturing plant in California. One solution is to include a
LocationType and Parent field in tblOrganization. For simplicity, look at
LocationType as Headquarters or Branch. The GM headquarters would have
headquarters in the LocationType field and an empty Parent field. The
California plant would have Branch in the LocationType field and the
OrganizationID of the parent in the Parent field. The California plant will
have a different OrganizationID than the headquarters but will be related to
the headquarters through the Parent field.
Click to expand...

I guess I'm looking for a way to
1 - have one address table
2 - have a table of 'contacts' - preferably people and organizations both
3 - have a way of linking a peoplecontact to an orgcontact. I suspect #2
& #3 are mutually exclusive, because of a circular logic problem or
something. At any rate my brain is trying to make them mutually exclusive!

I appreciate the help - Lisa
 
:

I was hoping to have just one address table to eliminate possible duplicates.

Right now our main table is contacts - we have a total of about 4400
entries, maybe 3% of which are organizations. Everything else is driven by
contactID (payments, event registrations, etc.) so I don't think we can make
that big a change.

I guess I'm looking for a way to
1 - have one address table
2 - have a table of 'contacts' - preferably people and organizations both
3 - have a way of linking a peoplecontact to an orgcontact.
I suspect #2 & #3 are mutually exclusive, because of a circular logic problem or
something. At any rate my brain is trying to make them mutually exclusive!
Click to expand...
It appears that you have contacts who attend events such as seminars and you
use this database to register them for an even, bill them and record
payments, possibly some marketing to past participants.

I am guessing that your table tblContacts has fields such as: ContactID,
First, Last, HomeStreet, HomeCity, HomeZip, HomePhone, Company,
CompanyStreet, CompanyCity, CompanyZip, CompanyPhone. Some of these are
required: ContactID, First, Last. Some can be empty. Some contacts will
have Home address information. Some will have Company Information. Some
will have both. Even though this is a wide table with a lot of fields, there
doesn't seem to be duplication of information except where you have more than
one Contact from a company with Company information, then the company
information will be duplicated. This duplication could be a problem in the
future.

Point 3: have a way of linking a peoplecontact to an orgcontact.

You can add a couple of fields to your contacts table: HasCompany(Yes/No)
and CompanyID. That way queries could pull up records where individuals have
company information.

Sorry I can't be of more help. Not sure I understand your needs.
 
Hi Roxie - thanks for the help. Your ideas on 'Has Co' look good. I feel
like I"m new to Access, even though it's been a couple years . . . but I'm
trying to normalize the database, and it seems like having 4200 of 4400
records with blank fields for company info is wrong. It seems to work OK, or
well enough. Right now I have a board member who wants mail at the work
address, not home, but at some point I'll have someone who wants their
newsletter at main address, membership invoice at another address, and car
show notices at a third address. (This is a worst case scenario . . . .) I
do presently have several contacts at the same work address, and a landlord &
tenant who both want to get their own copy of the car show notice, but the
bulk of what I do is 1 contact : 1 address. I don't allow duplicates in the
1st line of the address, so I have to enter PO Box 15 AND P.O. Box 15 AND P.
O. Box 15 when three contacts have the same address, or the same PO Box in
different towns, for example. In general, work-arounds are good until someone
else is doing data entry. . . .

regarding "Not sure I understand your needs" -- I was trying to stay general
and not overload anybody with specifics. Thanks for your advise - let me know
if you'd like more information or have other suggestions. Thanks! - Lisa
 
LReber said:
Hi Roxie - thanks for the help. Your ideas on 'Has Co' look good. I feel
like I"m new to Access, even though it's been a couple years . . . but I'm
trying to normalize the database, and it seems like having 4200 of 4400
records with blank fields for company info is wrong. It seems to work OK, or
well enough. Right now I have a board member who wants mail at the work
address, not home, but at some point I'll have someone who wants their
newsletter at main address, membership invoice at another address, and car
show notices at a third address. (This is a worst case scenario . . . .) I
do presently have several contacts at the same work address, and a landlord &
tenant who both want to get their own copy of the car show notice, but the
bulk of what I do is 1 contact : 1 address. I don't allow duplicates in the
1st line of the address, so I have to enter PO Box 15 AND P.O. Box 15 AND P.
O. Box 15 when three contacts have the same address, or the same PO Box in
different towns, for example. In general, work-arounds are good until someone
else is doing data entry. . . .
Click to expand...
You're right in trying to normalize the database. After looking at your
worst case scenario you might try something like:

tblContact with ContactID the primary key and foreign keys of NewsletterID,
BillingID and InvitationID plus the contact name fields.

tblNewsletter that is probably your default or main address. It would have
NewsletterID as primary key and ContactID as foreign key, and the the address
fields such as street, city, state, zip.

tblBilling for use when billing address is different from main. BillingID
as primary key and ContactID as foreign key address fields.

tblInvitation set up the same as the others.

For data entry I based the main form on tblContact with subforms based on
the other tables, linked by contact ID. I hid the billing and invitation
forms, to be called by command buttons if necessary.

Mailing labels for newsletter(main) is straightforward query on tblContact
and tblNewsetter.

Quick and definitely dirty, but not fully worked out is invoices. A
maketable query based on tblContact and tblNewsletter to select name from
contact and address from Newsletter but only those that don't have an entry
in BillingID in main table. Then an append query based on tblContact and
tblBilling to add records to the table you just made.

I didn't try the same process for invitations but assume it should work,
even though adding one doesn't always work for a juggler.

Good luck.
Roxie Aho
roxiea at usinternet.com
 
Back
Top