Issue regarding mailing addresses in tables

[Amit]

Hi,

I'm designing tables to store informaion on organizations
and organization representatives; and staff. Currently, I
have
1. person table that includes common fields for staff and
organization representatives, including their mailing
address
2. staff table to store information on staff (these are
fields relevant to staff)
3. representative table to store information on
representatives (fields relevant to representatives)
4. organization table (information on organization,
including the mailing address)

There is some duplication because many staff will have the
same mailing address. Also, there is duplication of
address between the organization table and the
representative table (representatives working for org A
will have the same mailing address).

I'm unable to decide if I should keep the design as is
(with some duplication), or make a separate table
called "Address table", and then include the "Address
Primary Key" in the other tables?

Having a separate Address Table will reduce the typing
somewhat as the address can be looked up, instead of re-
typing it. It will also result in extra code to handle a
new address (either add it to the Address table after
typing it, or have a separate form to enter a new address
if not in the list). I also remember reading an article at
Microsoft that it is recommended to not have a separate
Address table, even though it is theoretically pure.

Any thoughts on this will be appreciated.

Thanks in advance.

-Amit

 

[Adrian Jansen]

Handling much the same thing myself at the moment. Contacts with both
postal and street addresses, plus Organisations also with both, and of
course the Contacts belong to the Organisations... My latest thoughts are
to have all the Addresses in one table, with a FK in two different fields
for Organisation and Contact, and a field for Address Type ( postal or
street ). Then although there will be lots of duplicated addresses, if a
Contact moves, you can assign him a new address without upsetting he address
of the Organisation he belonged to. Even though there are duplications, in
reality the Contact address is a different piece of data from the
Organisation address. Having different address types also allows for
Organisations with accounts, delivery, etc different addresses, as well as
Contacts with home, work etc addresses.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[TC]

(snip)

There is some duplication because many staff will have the
same mailing address. Also, there is duplication of
address between the organization table and the
representative table (representatives working for org A
will have the same mailing address).

Looking at the second issue, does each reresentative *always* have an
address?

If so, perhaps you could have an address field in each table, but then use
the following rule: if the representative's address is blank, use the
address of the organization to which he belongs, instead.

You could even embody that rule in a query (for display/reporting purposes):

select iif (isnull (rep.address), org.address, rep.address) as
actual_address, rep.*
from representatives as rep, organizations as org
where org.orgid = rep.orgid

HTH,
TC