Help Normalizing an Address

While working with a database that contains subcontractors I came
across an interesting scenario with the respect to their addresses.
Currently all fields for the required data are in the one table and is
causing some problems with regards to updating the redundant info
across multiple entries.
Each sub-contractor can have one or multiple addresses. Main Address,
Remit To Address, Correspondance Address, etc...
Simple enough, but...
Some sub-contractors sell their receivables to a factoring company, so
in addition to typical address info, we must also have phone, fax,
contact info, etc., on hand in these situations.
Also, of the sub contractors that use a factoring company, (about 10%)
use the same factoring company. Redundant info.
Also, some sub contractors are a satilte location of a parent company
and althought their phyical address is different, their 'remit to' is
actually their parent co's remit to address.
Main (physical) addresses are important because we link their zip code
to lat/long database to find sub contractors closest to the particular
area of the country where a job needs to be done.
Right now we just have remit to fields in every sub contractor record.
When a Factoring company changes it's address, though, it can be a pain
to update.
Any suggestions?Without knowing how many records you are talking about, I think it would be
easiest to have one address table with a flag column indicating the type of
address and a fk column pointing back to the sub contractor. From an
administration standpoint, this would be easiest.
If you really feel the need to normalize out, then create a table with all
the unique addresses and use their UID as a Fk back to the sub contractor
table. This way when a new address comes in or an existing one is modified,
you can drop the old one and add the new one, while updating subs address
pointers.
"Clyde Venhause" wrote:

> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It seems to be a basic Many-to-Many consideration. At first thought I'd
do it like this:
CREATE TABLE ContractorAddresses (
contractor_id integer not null references Contractors ,
address_id integer not null references Addresses ,
address_type_code integer not null references AddressTypes ,
CONSTRAINT PK_ContractorAddresses
PRIMARY KEY (contractor_id, address_id, address_type_code)
)
The table Address will not have a ContractorID, 'cuz the above table
provides that info. The table AddressTypes will hold data like this:
address_type_code description
-- --
1 Remittance
2 Correspondence
3 Main Office
.. etc. ...
The communication info should be in a different table, much like the
ContractorAddresses table.
CREATE TABLE ContactorContacts (
contractor_id integer not null references Contractors ,
contact_id integer not null references Contacts ,
contact_type_code integer not null references ContactTypes ,
CONSTRAINT PK_ContractorContacts
PRIMARY KEY (contractor_id, contact_id, contact_type_code)
)
The ContactTypes table would have data like this:
contact_type_code description
-- --
1 Purchaser
2 Inspector
3 Lawyer
.. etc. ...
The Contacts table will hold info on who to contact:
CREATE TABLE Contacts (
contact_id integer not null UNIQUE ,
person varchar(30) not null PRIMARY KEY,
.. other columns ? ... -- may change PK requirement
)
A phones table for the contacts:
CREATE TABLE ContactPhones (
contact_id integer not null references Contacts ,
phone_type_code integer not null references PhoneTypes ,
phone_nr char(10) not null,
CONSTRAINT PK_ContactPhones
PRIMARY KEY (contact_id, phone_type_code, phone_nr)
)
The PhoneTypes table would have data like this:
phone_type_code description
-- --
1 Fax
2 Office
3 Purchasing Office
.. etc. ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmKiW4echKqOuFEgEQKgUACdF8x/K9jakhVTRe6okJI7qtFevNoAn0ky
DEbKy122rQJN1FlCOcaXp9jq
=gg/j
--END PGP SIGNATURE--
Clyde Venhause wrote:
> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>