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?
>

Help Needed Configuring ODBC

In the Office we have a Win2000 Server running MS SQL Server 2000. I know
the servers external IP address (xxx.xxx.xxx.xxx) and we have a sub-domain
set up (subname.mycompserver.com). I have a login and password into the
server as well as the SQL login and password. The firewall is open on port
1433. From my office PC, I can access SQL, using ODBC, with Access or my
Perl programs.
At home, I'm running WinXP Pro. Although it shouldn't have been necessary,
I installed MS SQL Server client. I connect to the Internet using a Comcast
cable modem and do not have a static IP address. Whenever I try to set up
ODBC to access my office's SQL server using ODBC Data Source Administrator
/ Add SQL Server, it fails with the following error message:
Connection Failed
SQL State: '01000'
SQL Server Error: 10060
[Microsoft][ODBC Server Driver][TCP/IP Sockets]Connection Open (Connect())
Connection Failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC Server Driver][TCP/IP Sockets]SQL Server does not exist or
access denied.
Is it possible to establish this ODBC connection into SQL and, if so, what
am I doing wrong. Thanks.
Fred
Hello Fred,
The only thing I can think is that firewall at your work is blocking 1433. The Connect() message means that a TCP/IP
socket was not created. From your office's server machine go to http://aboutmyip.com and see if port is open.
Fred Goldberg wrote:

> In the Office we have a Win2000 Server running MS SQL Server 2000. I know
> the servers external IP address (xxx.xxx.xxx.xxx) and we have a sub-domain
> set up (subname.mycompserver.com). I have a login and password into the
> server as well as the SQL login and password. The firewall is open on port
> 1433. From my office PC, I can access SQL, using ODBC, with Access or my
> Perl programs.
> At home, I'm running WinXP Pro. Although it shouldn't have been necessary,
> I installed MS SQL Server client. I connect to the Internet using a Comcast
> cable modem and do not have a static IP address. Whenever I try to set up
> ODBC to access my office's SQL server using ODBC Data Source Administrator
> / Add SQL Server, it fails with the following error message:
> Connection Failed
> SQL State: '01000'
> SQL Server Error: 10060
> [Microsoft][ODBC Server Driver][TCP/IP Sockets]Connection Open (Connect())
> Connection Failed:
> SQL State: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC Server Driver][TCP/IP Sockets]SQL Server does not exist or
> access denied.
> Is it possible to establish this ODBC connection into SQL and, if so, what
> am I doing wrong. Thanks.
> Fred
|||I truly appreciated your prompt reply to my question. Your answer
implies that this is doable.
I connected to our server using Remote Desktop, brought up IE and ran
http://aboutmyip.com. And, as you expected, Port 1433 is closed. My MIS
guys swore they opened it for me.
I'll have to get the port opened and try again. Thanks so much.
Fred
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Help me!!!

sSQL = "INSERT into order (, [pass], [pass1], [brands], [models], [attention], [address], [town], [code], [county], [country], [dp1], [p2], [fx], [email]) values ('" & i & "', '" & pass & "', '" & pass1 & "', '" &_
brands & "', '" & models & "', '" & attention & ", '" & address & "', '" & town & "', '" & code & "', '" & county & "', '" & country & "', '" & dp1 & "', '" & p2 & ", '" & fx & ",'" & email & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\worldcar\car.mdb "
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode = 0
'Open the connection to the database
connection.Open(sConnString)
connection.execute(sSQL)<<<<line44

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.

Help me!!!

sSQL = "INSERT into order (, [pass], [pass1], [brands], [models], [attention], [address], [town], [code], [county], [country], [dp1], [p2], [fx], [email]) values ('" & i & "', '" & pass & "', '" & pass1 & "', '" &_
brands & "', '" & models & "', '" & attention & ", '" & address & "', '" & town & "', '" & code & "', '" & county & "', '" & country & "', '" & dp1 & "', '" & p2 & ", '" & fx & ",'" & email & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\worldcar\car.mdb "
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode = 0
'Open the connection to the database
connection.Open(sConnString)
connection.execute(sSQL)<<<<line44

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.