February 4, 2005 at 7:00 pm
I have a table with the following columns:
tblSponsor
----------
sponsorID - pk
fname
lname
add1
add2
city
state
zip
I've created a generic address table to hold all kind of addresses. So the above table will be split into two like this:
tblSponsor
----------
sponsorID - pk
fname
lname
addressID - fk
tblAddress
----------
addressID - pk
addressType
add1
add2
city
state
zip
The original table already has data. So I want to write a SP which would perform the following:
- read tblSponsor
- create a record in tblAddress and save the pk
- create a record in tblSponsor (new) with addressID as saved pk
- repeat till eof reached in the old tblSponsor
How?
February 4, 2005 at 10:51 pm
You logic may not be correct. For example if a sponsor has more than one address, this is not going to work because you can only store one addressID in a sponsor record.
----------
sponsorID - pk
fname
lname
tblAddress
----------
sponsorID - fk pk
addressType pk
add1
add2
city
state
zip
Do you already have multiple addresses for the same sponsor in your current table?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 5, 2005 at 11:47 am
I replied to this thread but it seems to have been lost. So I'll do it again.
No. I will ALWAYS have one address per row. However, if I had multiple addresses, you are absolutely right. My design would fail.
I just want an SP for my design.
February 5, 2005 at 2:38 pm
You would populate the address table from the DISTINCT addresses of you current sponsor table:
INSERT INTO tblAddress
(add1, add2, city, state, zip)
SELECT DISTINCT
add1, add2, city, state, zip
FROM tblSponsor
Then add the addressid column to tblSponsor and run an UPDATE to set the ID's
UPDATE tblSponsor
SET addressID = a.addressid
FROM tblSponsor as s
INNER JOIN tblAddress as a
ON (a.add1 = s.add1 AND
a.add2 = s.add2 AND
a.city = s.city AND
a.state = s.state AND
a.zip = s.zip)
That's it, just drop the old address columns from tblSponsr and create your foreign key.
(Note, you didn't specify if address parts can contain NULLs - if they can be null, you'll need to
modify the join in the Update to allow for that)
February 7, 2005 at 10:19 am
wouldnt it be easier to have the sponsorID in the address table like Kathi Kellenberger suggested? then there is not need for a complecated update with a list of conditions.
February 7, 2005 at 10:25 am
No, this was confusion based on the column names. "add1 and "add2" are not 2 separate addresses, but are 2 lines of the same address (eg apartment number, street number).
You can't have SponsorID in the address table because that would reverse the required relationship.
It needs to be 1:N from address->sponsor, not the other way around, which is what SponsorID in the adress table would give you.
February 7, 2005 at 4:32 pm
What is the point of splitting off the addresses into a seperate table? If each sponsor can have only one address then it is an attribute of the sponsor and should logically reside in the same table. Now, it could (should) be argued that since city and state are not completely dependent on the sponsor, they should be in their own tables. i.e. City is (at least partially) determined by Zip Code, and state is determined by City. The problem with this approach is that Zip Codes may "have" several cities, and some cities may have many zip codes, so it tends to get a bit messy, and maintaining that data is a real challenge (or it's expensive to buy it). Although if addresses are critical to your business, a good address standardization/validation system can be vital and will greatly simplify the maintenance issue.
Generally, I recommend storing the address field as a single field rather than address1 and address2 (per the USPS standard) along with a Zip Code, and CityID (this is often a good place to use a surrogate key) from which the State can easily be derived all on the Sponsor table.
I have seen the use of "address" tables in a number of systems, and they generally do little more than make your queries more expensive and difficult to write. They generally stem from a misunderstanding of what normalization really is.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 7, 2005 at 7:13 pm
The address table is a common table to hold different types of addresses. tblSponsor is just one table. There will be more tables having addresses for which this table will be used. Custormer Contacts, Vendor Contacts, etc. to name a few. Each of these tables will have a column AddressID that will be a fk pointing to this table.
February 8, 2005 at 10:38 am
Just a few more pennies worth,
If your intent is to have available a search of all of the addresses no matter what the contact type (Sponsor, vendor, etc) then your method is ONE way to go.
I would not however store a pointer to the address table in each of the Tables (Sponsor, Vendor). This implies that the address is the parent to the sponsor.
Your Primary key for the address would be made up of the Addresstype (sponsor, vendor) and the ID of that Parent.
Better yet you could have a UniqueIdentifier as the primary key in your parent tables then you could mix the addresses all together and not need the second column.
This is a lot of work to get your addresses all together. You may want to think about having them in their respective tables and then creating a view to query them.
tal
February 10, 2005 at 12:41 pm
CREATE PROCEDURE dbo.Sponsor_Create_Address
AS
SET NOCOUNT ON;
INSERT INTO tblAddress(sintAddressTypID,
nvcAddress1,
nvcAddress2,
nvcCity,
chrStateCode,
nvcZip,
nvcASCU)
VALUES(
select 4,AddressLine1,
AddressLine2,
City,
State,
Zip,
'aaa'
from tblxxx
)
SELECT SCOPE_IDENTITY();
GO
The above SP gives me a syntax error. Also, this is not a completed SP. What I want is save SCOPE_IDENTITY() so that I can update tblSponsor with it.
February 10, 2005 at 12:58 pm
Try something like this...although I still think it's not a smart thing to split your addresses off like this given what you've said about the subject.
CREATE PROCEDURE dbo.Sponsor_Create_Address
AS
SET NOCOUNT ON;
DECLARE @ID int
BEGIN TRAN
INSERT INTO tblAddress(sintAddressTypID,
nvcAddress1,
nvcAddress2,
nvcCity,
chrStateCode,
nvcZip,
nvcASCU)
SELECT 4, AddressLine1,
AddressLine2,
City,
State,
Zip,
'aaa'
FROM tblxxx
IF @@ERROR <> 0 GOTO ErrBlock
SET @ID = SCOPE_IDENTITY()
UPDATE tblSponsor
SET ColumnName = @ID
WHERE ...
IF @@ERROR <> 0 GOTO ErrBlock
COMMIT
RETURN
ErrBlock:
ROLLBACK
RETURN
GO
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 10, 2005 at 1:02 pm
CREATE PROCEDURE dbo.Sponsor_Create_Address
AS
SET NOCOUNT ON;
DECLARE @intAddressID int;
INSERT INTO tblAddress(sintAddressTypID,
nvcAddress1,
nvcAddress2,
nvcCity,
chrStateCode,
nvcZip,
nvcASCU)
select 4,
AddressLine1,
AddressLine2,
City,
State,
Zip,
'aaa'
from tblxxx;
update tblSponsor set
intAddressID = SCOPE_IDENTITY()
FROM tblxxx as x
INNER JOIN tblSponsor as s
ON (x.SponsorID = s.intSponsorID);
GO
This seems to work!
February 10, 2005 at 1:11 pm
Do not neglect the error checking and transaction control. If the first insert fails for some reason, your code would still attempt to update tblSponsor.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 10, 2005 at 1:44 pm
Thanks for the tip.
The following SP ALWAYS updates the intAddressID column in tblSponsor with the same value that is the last identity generated.
CREATE PROCEDURE dbo.Sponsor_Create_Address
AS
SET NOCOUNT ON;
DECLARE @intAddressID int;
INSERT INTO tblAddress(sintAddressTypID,
nvcAddress1,
nvcAddress2,
nvcCity,
chrStateCode,
nvcZip,
nvcASCU)
select 4,
AddressLine1,
AddressLine2,
City,
State,
Zip,
'aaa'
from tblxxx
set @intAddressID = SCOPE_IDENTITY()
update tblSponsor set
intAddressID = @intAddressID
FROM tblxxx as x
INNER JOIN tblSponsor as s
ON (x.SponsorID = s.intSponsorID);
GO
February 10, 2005 at 2:17 pm
Yes, that's my point. What if the INSERT fails? The UPDATE still happens but you will now be setting the addressID in tblSponsor to NULL.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply