October 14, 2009 at 6:44 am
I have a Customer Addresses table
CREATE TABLE dbo.CustomerAddresses
(
CustomerAddressID int IDENTITY,
AddressLine1 varchar(50) NOT NULL,
AddressLine2 varchar(50) NOT NULL,
City varchar(50) NOT NULL,
StateCode char(2) NULL,
ZipCode varchar(20) NOT NULL,
CountryCode char(2) NOT NULL
}
Note: This is not a complete copy of the table. I only display what I think is relevant to the question.
Let's say I have the following addresses in the CustomerAddresses table:
ID Address
10 10 Lost In Space Street.....
20 20 Fortess of Steel.....
I have several other tables that reference the address by using the CustomerAddressID as a Foreign key into the CustomerAddresses table.
Lets say the Customer moves from 10 Lost In Space Street to 20 Fortess of Steel.
Is there some way that I can automagically find each table that depends on the CustomerAddresses table and change the id in those records from 10 to 20?
My current solution is to change the stored procedure that 'moves' the customer so that it changes the address id in each of the child tables. This means that I have to modify this stored procedure each time I introduce a new table that depends on the CustomerAddresses Table. Is there a better way?
Thanks
October 15, 2009 at 5:48 am
Your requirement sounds weird. On the first instance why do u want to modify the CustomerAddressID i.e Primary Key value and that too and Identity column. Whenever the customer is changing the address, what you got to do is, only update the address column with the new address details and the rest of the references should be as it is
October 15, 2009 at 8:22 am
LovesSQLS (10/15/2009)
Your requirement sounds weird. On the first instance why do u want to modify the CustomerAddressID i.e Primary Key value and that too and Identity column. Whenever the customer is changing the address, what you got to do is, only update the address column with the new address details and the rest of the references should be as it is
The requirement is weird. In my initial design I did exactly as you suggested. When the address changed, I left the CustomerAddressID unchanged and everything was fine. Then I ran into a problem. When a customer moved and my users changed the address that had a Customer Address ID of 10
from: 10 Lost In Space
to: 20 Fortress of Steel
I wound up with the following:
ID Address
10 20 Fortress of Steel.....
20 20 Fortress of Steel.....
On the plus side, all of the dependant tables did not have to change as CustomerAddressID 10 now refers to the new address(Fortress of Steel ). On the negative side (the side my boss is upset about), I now have two customer address rows that point to the same address.
My customers change addresses so I have to allow for this situation. Is there some way around this problem?
Thanks, I appreciate the help.
October 15, 2009 at 12:26 pm
If I am understanding it correctly you have a many-to-many relationship between customers and addresses. If that is the case you may be better off if you create a third table to establish the relationship. For instance:
create table customer (customerID int primary key, customerName, ....)
create table Address (AddressID int primary key, Street varchar(255), Number varchar(255), ...)
create table CustomerAddress (CustomerAddressID int primary key, CustomerID int, AddressID int)
You can link all the other related tables to CustomerAddress table. If a customer moves to a different address all it changes is the AddressID of the CustomerAddress table, while the CustomerAddressID is the same and the data integrity is preserved.
October 15, 2009 at 1:11 pm
Al-279884 (10/15/2009)
If I am understanding it correctly you have a many-to-many relationship between customers and addresses. If that is the case you may be better off if you create a third table to establish the relationship. For instance:
create table customer (customerID int primary key, customerName, ....)
create table Address (AddressID int primary key, Street varchar(255), Number varchar(255), ...)
create table CustomerAddress (CustomerAddressID int primary key, CustomerID int, AddressID int)
You can link all the other related tables to CustomerAddress table. If a customer moves to a different address all it changes is the AddressID of the CustomerAddress table, while the CustomerAddressID is the same and the data integrity is preserved.
Wow. I wish I had thought of this myself.
Thank you so much.
October 15, 2009 at 1:25 pm
Using the CustomerAddress table is the correct way to go if you truely have a many-to-many relationship between Customers and Addresses, but using the CustomerAddressID to 'link' that value to other tables will be de-normalizing those tables and cause you massive headaches.
Al-279884,
Give me an example of how you propose to 'link' the CustomerAddress row to any other related table?
meichner,
Should a customer be able to have more than one address?
October 15, 2009 at 1:34 pm
John Rowan (10/15/2009)
Using the CustomerAddress table is the correct way to go if you truely have a many-to-many relationship between Customers and Addresses, but using the CustomerAddressID to 'link' that value to other tables will be de-normalizing those tables and cause you massive headaches.Al-279884,
Give me an example of how you propose to 'link' the CustomerAddress row to any other related table?
meichner,
Should a customer be able to have more than one address?
Yes, A customer could have more then one address. I am not a DBA so I am not sure what kind of headaches Al-279884's idea would cause.
Thanks
October 15, 2009 at 1:37 pm
Ok, so the proper way to represent the many-to-many relationship between Customers and Addresses is to have the associative table CustomersAddresses. Agreed. But you do not want to use the Surrogate Key from CustomersAddresses as a foreign key in your other tables.
October 15, 2009 at 1:55 pm
John,
From the short description of the problem it seems like every time you change an association between a customer and an address, you have to find all the records on all the related tables and change those records as well. To me it seems like the database is not in a proper normalized form. What I am proposing will probably normalize rather than de-normalize the database. Customer-address relationship is stored only once and not repeated on each related table.
Again, I should say that I am speculating a lot since I do not have any details on the other tables relations between them. The "other" tables will have a foreign key pointing to CustomerAddress.CustomerAddressID.
October 15, 2009 at 1:59 pm
That's fair. I think we need more info then from the OP as to how the addresses are currently being related to other entities.
meichner,
Care to handle this? In your current design, how are Customer Addresses being related to your other entities? Can you provide an example?
October 15, 2009 at 2:03 pm
John Rowan (10/15/2009)
Ok, so the proper way to represent the many-to-many relationship between Customers and Addresses is to have the associative table CustomersAddresses. Agreed. But you do not want to use the Surrogate Key from CustomersAddresses as a foreign key in your other tables.
John,
You are right, the surrogate key shouldn't be used as a foreign to other tables.
October 15, 2009 at 2:08 pm
Yes, and you are right that it sounds like that is already what is happening with meichner's existing Customer Address table. The Customer Address should only be related to the Customer since that is the parent entity. It sounds like the Customer Address is being used as a Foreign key in more than just the Customer table.....hence the problem. Normalization rules have been violated and we're seeing the update/insert/delete anomolies that result from breaking those rules.
I'm still curious as to a reply from meichner with more info.
October 15, 2009 at 2:26 pm
John Rowan (10/15/2009)
That's fair. I think we need more info then from the OP as to how the addresses are currently being related to other entities.meichner,
Care to handle this? In your current design, how are Customer Addresses being related to your other entities? Can you provide an example?
Hopefully I will be able to clarify. My Customers can have many addresses. When a sales order is being created we have to know which address to ship the item(s). When an invoice is created we have to know where to send the bill. Therefore in all the tables that need to know the address of where to send something we put the Address ID in those table. The problem that I ran into was that my customers change addresses from time to time.
In my address table I have the following data:
ID Address
88 1 Lost in Space, New York New York
99 8 Fortress of Solitude, North Pole
I have an entry in my Sales Order table that says Sales Order 12345 is being shipped to AddressID 88. We then get a call from the Customer saying they are moving to 8 Fortress of Solitude, North Pole.
Given this senerio I saw 3 choices.
1. Stop my user from changing the address because it was being used on a Sales Order. That didn't fly.
2. Change Address 88 to 8 Fortress of Solitude, North Pole. That would mean that Address 88 and 99 both point to the same place. My boss was very un happy with this.
3. Find all the Sales Orders that had an address ID of 88 and change them to 99. My boss loved this idea. I didn't like it for several reasons. The most obvious being - gee I forgot to change all the Address ID's on the Invoices (actually I didn't forget, I am just trying to demostrate the point I made to my boss). Using this method I would have to modify my stored procedure for every new table that relies on the Address.
My boss is under the impression that there is some t-sql magic that I could do so that I wouldn't have to modify the stored procedure everytime a new address dependent table was added to the system.
Does this make sense? I really appreciate the help.
Thanks
October 15, 2009 at 2:38 pm
Yep, it makes sense. I'm not sure if that's how I'd design it, but it is what it is. My personal preference would be to have a Customers table, and Addresses table, and an associative table CustomersAddresses. This table has a row in it for each address that a customer can have. I'd maybe go so far as placing an AddressType column in the CustomersAddresses table, linking this to yet another table AddressTypes. Here you can set an address type for each of the Customer's Addresses. So you could have the following types: Shipping, Billing, Other (etc.). You could then constrain your associative table so that each Customer can only have 1 entry for each type of address.
Now, to relate the addresses to the Orders, you don't use the AddressIDs. The Order does not belong to an address, it belongs to the Customer. You link the Customer to the Order and you can then derive the proper shipping/billing addresses via a JOIN between the Orders>>Customers>>CustomersAddresses table. Any edits made to the Address flow directly do not need to be cascaded to your other tables.
You could leverage this same design out for use with addresses for other entities. Say you have a Manufacturer entity that needs to have addresses tied to it. The addressses live in the Addresses table and you create another associative table ManufacturersAddresses table to represent the many-to-many relationship between the Manufacturers and their Addresses.
You may consider changing schema, but if you are too far past this, you'll be stuck with editing all of your stored procedures each time you tie an address to another entity.
October 15, 2009 at 2:39 pm
meichner (10/15/2009)
John Rowan (10/15/2009)
That's fair. I think we need more info then from the OP as to how the addresses are currently being related to other entities.meichner,
Care to handle this? In your current design, how are Customer Addresses being related to your other entities? Can you provide an example?
Hopefully I will be able to clarify. My Customers can have many addresses. When a sales order is being created we have to know which address to ship the item(s). When an invoice is created we have to know where to send the bill. Therefore in all the tables that need to know the address of where to send something we put the Address ID in those table. The problem that I ran into was that my customers change addresses from time to time.
In my address table I have the following data:
ID Address
88 1 Lost in Space, New York New York
99 8 Fortress of Solitude, North Pole
I have an entry in my Sales Order table that says Sales Order 12345 is being shipped to AddressID 88. We then get a call from the Customer saying they are moving to 8 Fortress of Solitude, North Pole.
Given this senerio I saw 3 choices.
1. Stop my user from changing the address because it was being used on a Sales Order. That didn't fly.
2. Change Address 88 to 8 Fortress of Solitude, North Pole. That would mean that Address 88 and 99 both point to the same place. My boss was very un happy with this.
3. Find all the Sales Orders that had an address ID of 88 and change them to 99. My boss loved this idea. I didn't like it for several reasons. The most obvious being - gee I forgot to change all the Address ID's on the Invoices (actually I didn't forget, I am just trying to demostrate the point I made to my boss). Using this method I would have to modify my stored procedure for every new table that relies on the Address.
My boss is under the impression that there is some t-sql magic that I could do so that I wouldn't have to modify the stored procedure everytime a new address dependent table was added to the system.
Does this make sense? I really appreciate the help.
Thanks
Then probably you would want to store the CustomerAddressID in the Orders table. All the other tables (ShippingInfo, Billing, etc) should have a foreign key to Orders.OrderID. Actually you may have both ShippingCustomerAddressID and BillingCustomerAddressID columns in your Orders table for orders shipped and billed to two different addresses. Then, if a customer changes his address you simply change the CustomerAddressID in your Orders table and everything else will be rerouted automatically to the new address.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply