Normalization: Tables for Addresses?

[Peter Stone]

Novice/WinXP/Access 2003
Good Evening

With good advice from this forum, I have a normalized db with one-to-ones,
one-to-manys, and many-to-manys.

But, how do I normalize addresses. Northwind db doesn't help because they
give everyone a one-line street address and one phone number.

Each address is attached to an organization.

I presume:
tblStreetAddresses (with each line of the address in a separate record).
tblPostCodes (with PostCode as ID and fields for IDCity and Suburb).
tblTelephones (with fields Telephone and TelephoneType).
tblEmails
tblCities

Sometimes people share the same address and telephone numbers.

Will there be problems if one line of the address is the same as a different
address?

Do I allow duplicates in the address field? There could be a 22 Rose Street
in different suburbs.

Room 15
22 Rose Street
Any Suburb

Room 99
22 Rose Street
Any Suburb

22 Rose Street
Some Other Suburb

Or should the StreetAddress be in one field with lines separated by commas?

Should the street number be in a separate field?

On the Net, I saw advice to put words like Street and Avenue in a separate
table...surely that's not necessary.

Thanks

Peter

 

[David F Cox]

Here we come into "business rules" considerations. Someone has to decide how
many phone numbers, fax numbers, addresses etc you allow for each contact.
In many cases it will be one of each type, where type may be , for example,
office Phone, Home Phone and Mobile. If you business rule is "only one of
each" then you can put those three items as fields in you contact record.
There is a one-to-one relationship set up under the business rules.

On the matter of addreses you could take the view that what gets written on
an envelope is the address, and have that as one text field. It is generally
far more useful to have the address as seperate lines. These seperate lines
have a one-to-one-relationship with what gets written on the envelope, so it
is alright to have them as separate fields within the same record.

It is true that several adresses will share the same town name, or street
name, but it is usual not to separate them into another table. That way lies
madness. You could take the same view of dates of birth, and have a table of
all of those with foreign keys pointing to them. It would not be sensible.

If the business rules allow an unlimited number of conact details, such as
wanting to know all of an employees email addresses, then those could
properly be recorded in a separate table, one per record.

 

[Peter Stone]

Thanks for the prompt reply David.

It confirms what I was thinking, but as a novice you always have doubts.

And thanks for the explanation why, because it enables me to make the
decision for myself next time. I have some Access manuals, but they are long
on How and short on Why.

Much Appreciated

Peter

 

[Jeff Boyce]

Peter

In addition to David's observations, I'll offer the business scenario that
drove the "contacts" db we had to create...

One Person can have many Addresses (e.g., home, work, shipping, ...)

One Address can have many Persons (e.g., business address, multiple
contacts)

One Person can serve in many Contexts (e.g., roles)

Each Person-Context can have many Addresses

One Person can be a Member of many Groups

Each Person-Member-Group can have one or two Addresses (i.e., mailing,
shipping)

I created a Context table (person, role, organization), an Address table
(one field per fact), a ContextAddress table (to resolve the many-to-many),
a Group table and a GroupMember table (to resolve the many-to-many).

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Douglas J. Steele]

In addition to all the advice you've already received, take a look at
http://www.databaseanswers.org/data_models/customers_and_addresses/index.htm

 

[Peter Stone]

Thanks Jeff and Douglas
My current address tables are fairly simple: one organization, one address,
one email, maybe two telephones.

Next I am tackling another part of my project that includes contacts: with
the possibility of one person having several addresses etc. etc. etc.

Your invaluable contributions have forseen the questions I was going to ask
next.

Thank you again

Peter