redundant data - help with Contact Database

[Jacqueline]

I have a database that has one table right now, called "Contacts". That
table has fields like Last_Name, First_Name, Address1, City1, State1, Zip1,
Organization, etc. Many of our contacts work within the same organizations
(i.e. California State University Monterey Bay, Monterey Bay Aquarium, etc.)
I want to avoid entering the address for these organizations for each contact
we have within that organization. What is the best way to design the
database where I only enter the address for these organizations once, and
link the address to the contact at that organization?

I took a stab at it and created an "Address" table that contains the
addresses for the organizations we have a lot of contacts at. I tried to
create a relationship where our Contacts Table pulls in the foreign key for
the Address Table, but it seems weird because 1) most of our contacts have
their own unique address and aren't part of one of these big organizations,
and 2) my Contacts Table already has Address, City, State, Zip fields, so
linking the contacts with our major organizations to the Address Table, it
seems like I need to pull in the Address, City, State, Zip, etc from the
Address Table for these contacts, which then adds a second new Address, City,
State, Zip fields to our Contacts Table that already exists with these
fields.

Any help on how to simplify this would be appreciated!
Thank you!
Jacqueline

 

[Jeff Boyce]

Jacqueline

You want "simple" for what is not a simple situation...

It sounds like you are describing a many-to-many situation. You have
contacts (?always a specific individual?), you have addresses, and you have
Contact-at-Address. That requires three tables (the third one resolves the
many to many situation).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Hello Jacqueline,

Your post didn't answer one key question: Do you (really) need to track the
scenario where one person is a part of several different organizations? If
you can get by with "no" your life will be simpler. Jeff gave a tip on how
to deal with that aspect if the answer is "yes".

Your post can't be answered without an answer to above question and others
which you probably haven't thought about yet. Here are some questions plus my
temporary guesses at the answers:

The above question: "No" (no need to record one person being at more than
one organization.

Is your mission simply to record the mailing-address-to-use for each
person? Or, are you out to record their personal address (when known) plus
their relation to an organization (when such exists), plus the address of
that organization? I'm guessing on the latter

I'm assuming that your format of an address will be fundamenatly different
when mailing to the c/o of their organization (i.e. the organization's name
will be a part of the "address").

If my guesses were right, then your main structure / tables will be the
classic many-to-one, people at organizations one. Two main tables (of
course, shorten the field names):

"Organizations" Table
PK = OrganizationIDNumber
Fields for all of the organizations info (address etc)

"People" Table
FK= "OrganizationIDNumber" linke to it's namesake
Home or address fields
A "UseWorkAddress"(?) field (enter "Y" for yes, even if their home address
is also known) )
PK = "PersonIDNumber" (not used here)

If my guesses were right, this would provide a good foundation for your
solution.


Hope that helps a little

Sincerely,

Fred

 

[Jeff Boyce]

Actually, I wasn't even considering one person at many organizations
(although the design handles one person at many addresses). I was thinking
about my situation, in which many people share a single address.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jacqueline]

Thanks Fred and Jeff. I'm going to work on this using your suggestions and
see what I come up with. Your replies are helpful and just enough to leave
me dangerous!

Sincerely,
Jacqueline

 

[Fred]

Hello Jeff,

I figured that many people at one address was a done-deal, if for no other
reason than that the "one address" could be that of an organization.

I figured that you were covering the possibility of the reverse also being
true.....multiple addresses/organizations for one person. BTW ruling out
having to record that ( = recording only the one most-relevant/important
organizational link) have proven very workable for us and made our contact
canagement / CRM db's a lot simpler. (one-to-many vs. many-to-many)


Sincerely,

Fred

 

[Jeff Boyce]

Fred

Thanks! I'll need to take a closer look at the constraints our management
and users have imposed and see if I can 'finesse' the structure without them
noticing...<g>

Regards

Jeff