Address tables are a thorny problem in normalization terms. Sometimes I do
them one way, sometimes another.

Sure, you could have a separate table for Streets because storing the street
multiple times can introduce redundancy with all the problems that entails.
However, by that same reasoning, we should have a FirstName table and a
LastName table, because there can be multiple "Roger"s in the database and
also multiple "Carlson"s. However, having a FirstName and LastName table
has limited utility even though it adheres to strick normalization rules. I
think a Streets table falls into the same category.

I will often have City, State, and Zip tables. However, the relationships
here are complex too. Both Michigan and Minnesota have a city called Grand
Rapids. It's also possible for a single city to be in two states. Some
cities have multiple zip codes in them and some zip codes span multiple
cities. Working out the relationships so only the correct city, state and
zip combinations can be chosen is quite complex.

One step down from full normalization is to just have separate City, State,
and Zip tables that are used as lookup tables to keep spelling errors down.
Such a system relies on the user to correctly select the proper combination
of them. Humans can be quite good at this, and sometime you just have to
trust them. However, I generally don't extend this to Streets. I usually
keep the entire address in one field.

Now, if a person or business can have multiple addresses, I will create an
Address (or possibly Location) table because there is a definite one-to-many
relationship there.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Monet 138" wrote in message
...
Thanks for all the help, I learned a lot reading those documents.

I have two more questions the second of which depends on the answer to the
first.

1. The smaller I can keep the file size the better. So for fields such
as
STREET (where each can show up on multiple entries and many entries can
have
two STREET listings), would it be better to have a separate table holding
those and then link that to the LOCATION table or just have it stored
directly in the LOCATION table?

2. Many entries in the LOCATION table can have two STREET entries (listed
as Primary & Secondary). If STREET should be stored in its own table to
help
reduce file size, how do I link the same table to two different entities
(Primary & Secondary) on the LOCATION table?

Example: (Field A_Entry - Field B_Entry - Field C_Entry)
Valve_1 - Primary_A Street - Secondary_B Street
Valve_2 - Primary_A Street - Secondary_C Street
Valve_3 - Primary_C Street - Secondary_A Street

Thanks again in advance.
--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein