How do I rationalize our address tables?

[Frank Martin]

We are using Access2000 with WindowsXP for our business software and this
has worked very well for years.

On the debtors side we have five tables for addresses, each with an
appropriate AddressID.

It has occurred to us to alter this arrangement to have just one table for
all five address types, and then use Boolean switches to select an
appropriate address as required for an Account Address, Delivery Address,
Freighter Address, Site Address etc.

This would make it easier to use synchronized combos such as used to filter
for all Freight Addresses used by a particular customer.

Also, there would be only one table for address entry, which could be
assigned to a class by radio buttons.

Would someone please advise whether this is a good idea, and if not how else
to combine all address tables into one and still be able to filter for
addresses related to a particular customer?

Please help, Frank

 

[Frank Martin]

I think I understand what you mean if the method is the same as we use for
the customers' price lists, in which each customer has their very own price
list.

Your method is the same in that every address has its own customer, and so
may be shared with any customer at all, say for example if one Freighter is
used for servicing many customers. That is one address has many customers
and one customer has many addresses. Am I correct in assuming this?

If this is a many-to-many system, perhaps we need three tables, vis:

tblCustName
tblAddrType
tblAddresses

These three tables then consist of:

tblCustName:
CustID (PK)
AddrTypeID
(CustomerNames)

tblAddrType
AddrTypeID (PK)
AddressID (PK)
(AddressTypes)

tblAddresses
AddressID (PK)
AddressTypeID
(Addresses)

In the relationships screen, the tblAddressType links the many-to-many
relation between the other two tables.

Do you think this method will work?

Regards Frank