Table structure for addresses

[Adrian Jansen]

As part of a development, I have to provide a personal contacts table, with
the usual details, name, address, phone etc
I thought about ( hasnt everyone ! ) the problems of providing multiple
addresses, business, personal etc, plus the hassles of handling multiline
department, building, street, suburb, town, etc and realised I could end up
with 20-30 fields in the table, and still not cover all the possibilities.
Then I started thinking about doing it relationally, with three tables:

tblContacts
ContactID PK
Name
etc

tblAddressLines
LineID PK
ContactID FK to tblContacts
AddressTypeID FK to tblAddressType
LineNumber sequence number 1-n for multiline addresses
AddressDetail the actual text

tblAddressType
TypeID PK
Type text, (phone, email, postal, business, etc)

Then tblAddressLines is a 1-M relation with both tblContact and
tblAddressType
so by setting the address type, you can have as many address lines as you
like, sorted by LineNumber to keep the order correct for printing, and also
multiple addresses, without having many fields in the contact table.

Seems like it ought to work, but I am looking for confirmation that its a
sensible way to go, before committing too much work.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Jeff Boyce]

Adrian

Your tblAddressLine is serving the function of a "relation" table, sort of.
That is, you have a (potential) many-to-many relationship between
individuals and addresses.

For example, I have more than one address, and more than one person shares
each of the addresses.

A possible down-side to your design is the lack of an easy way to find all
the addresses in City = New York, or State = Oregon, or all the Postal Codes
starting with "98".

Another approach would be to create an address table with as many fields as
you will need (see below), bearing in mind that the postal service may have
some limits to how many lines you can put on a mailing label. You can
combine any number of fields into the appropriate (maximum) number of lines
by concatenating the fields in a query.

tblAddress
AddressID
DeliveryAddress (may be a "street address", may be a PO Box, ...)
SteRmAptFl (suite, room, apt, floor - a "number" or letter)
BuildingFacility (some addresses include "Building 5")
City
State
Country
PostalCode (to handle both US and non-US variations)

Now, to resolve the many to many relationship, add a table:

trelPersonAddress
PersonAddressID
PersonID
AddressID
AddressTypeID

Note that I've put your AddressType into the relation/resolver table -- the
same address could be "used" by more than one person, so you need to be able
to have each person "using" a given address(ID) in a different way (i.e.,
Type).

Good luck!

Jeff Boyce
<Access MVP>

 

[Adrian Jansen]

Thanks for that Jeff,

Yes I saw the problem with sorting when I thought about it more...

At least your solution has only one address table, not the mess that results
when you try to have 1,2,3... address blocks within the one Contact table.
But I can still see the potential for an unforseen field needed later with a
multi-field address table. I did think about putting the main part of the
address in a memo field, so that would allow multiline entries - and I take
your point about printing labels, but much of the address info is not
necessarily for mailing. Of course you cant sort on parts of a memo field
either, but that may not be a problem, if we keep the suburb/city/postcode
as separate fields. I have seen several references to memo fields not being
a good idea, particularly in multi-user environments ( which this is ).

But I am still trying to get away from having a fixed number of fields for
the address table.

Maybe I add a SortType field to the AddressLines table, so the user can
specify that a field is to be sortable, eg 1 for postcode, 2 for city, etc
A combo box on the form would handle this ok, at the expense of a bit more
user input. And then I can write queries for sorting based on the SortType
field, if its not null.

The scenario is that the company concerned can mail out a variety of
documents, ranging from single sheets to bulky multi-page notes, and need to
be able to specify for each contact, and for each type of mailout, how the
article is to be delivered, eg email, fax, snail-mail, etc. I can set up
the tables for that ok, with a few M-M relations, but it struck me that I
could do at least as well with the addresses too. Then an 'address' may be
anything from a fax number to a multiline postal address, all handled by the
same data structure.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Adrian Jansen]

Thanks again Jeff,

Yes, separate tables for the different entities is a possible, although from
the perspective of a mailout, the postal address and the email address ( or
even fax number ) are the same type of data, even though we tend to think of
them as different.

I think I will go to your suggestion of an address table, with possible
multiple entries for the same contact. That should do, with some extra
fields for complex address stuff, and its certainly a bit easier to
implement than the single line address idea I had, especially if I want to
include sorting capabilities.

I will stay away from memo fields.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control