October 13, 2009 at 6:36 am
I have the following two tables:
CustomerAddresss and CustomerAddressesXref.
For each CustomerAddress, I might have one or more CustomerAddressesXref records. What distinguishes one CustomerAddressesXref row from another is the Customer Number and TypeCode. The type code represents a shipping type, billing type, corporate type... etc. This means that I might have several different customers or types that point to the one CustomerAddress.
I am in the process of building a Sales Order table. Each sales order points to one billing address.
1. Should the field in the Sales Order table be dependent on the CustomerAddresses table or the CustomerAddressesXref table?
2. If it should be the CustomerAddressesXref table should I add an identity column to the CustomerAddressesXref table so that the Sales Order table can use this field as its foreign key into the CustomerAddressesXref table?
Thanks so much - The tables are listed at the bottom of this post.
CREATE TABLE dbo.CustomerAddresses
(
CustomerAddressID int IDENTITY,
CompactUniqueAddress varchar(120) NOT NULL,
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,
RowVersion int NOT NULL,
LastChangedDateTime datetime NOT NULL,
OperID int NOT NULL,
CONSTRAINT PK_CustomerAddresses
PRIMARY KEY CLUSTERED (CustomerAddressID),
CONSTRAINT Unique_CustomerAddresses
UNIQUE NONCLUSTERED (CompactUniqueAddress),
CONSTRAINT FK_CustomerAddresses_CountryCodes
FOREIGN KEY (CountryCode)
REFERENCES dbo.CountryCodes (CountryCode),
CONSTRAINT FK_CustomerAddresses_USStates
FOREIGN KEY (StateCode)
REFERENCES dbo.USStates (StateCode)
)
CREATE TABLE dbo.CustomerAddressesXref
(
CustomerNumber int NOT NULL,
TypeCode int NOT NULL,
CustomerAddressID int NOT NULL,
SendTo varchar(50) NOT NULL,
ShipLabelNumber int NULL,
Memo varchar(100) NULL,
CONSTRAINT PK_CustomerAddressesXref
PRIMARY KEY CLUSTERED (CustomerNumber,TypeCode,CustomerAddressID),
CONSTRAINT FK_CustomerAddressesXref_Customers
FOREIGN KEY (CustomerNumber)
REFERENCES dbo.Customers (CustomerNumber),
CONSTRAINT FK_CustomerAddressesXref_TypeCodes
FOREIGN KEY (TypeCode)
REFERENCES dbo.TypeCodes (TypeCode),
CONSTRAINT FK_CustomerAddressesXref_CustomerAddresses
FOREIGN KEY (CustomerAddressID)
REFERENCES dbo.CustomerAddresses (CustomerAddressID),
CONSTRAINT FK_CustomerAddressesXref_ShipLabelNumbers
FOREIGN KEY (ShipLabelNumber)
REFERENCES dbo.ShipLabelNumbers (ShipLabelNumber)
)
October 13, 2009 at 7:07 am
1. CustomerAddressesXref table
2. Yes
October 13, 2009 at 7:21 am
Bradley Jacques (10/13/2009)
1. CustomerAddressesXref table2. Yes
Can you explain why the dendency should be on the CustomerAddressesXref and not the CustomerAddresses table?
Thanks
October 13, 2009 at 9:00 am
1. Should the field in the Sales Order table be dependent on the CustomerAddresses table or the CustomerAddressesXref table?
2. If it should be the CustomerAddressesXref table should I add an identity column to the CustomerAddressesXref table so that the Sales Order table can use this field as its foreign key into the CustomerAddressesXref table?
-- I apologize, I acutally ment the CustomerAddresses Table. The field should be dependent on the addresses table because the order is specific to the address. At some point you may want to write a query that says "Find all the sales orders to this address with this customer" You will no be able to do that if you base the FK on the CustomerAddressesXref table.
-- The identity column is usually the best way to ensure that there is a unique value to represent each of the fields. You do have a PK on the table that's unique; however, I personally prefer the identity column for visual purposes.
October 13, 2009 at 9:19 am
Bradley Jacques (10/13/2009)
1. Should the field in the Sales Order table be dependent on the CustomerAddresses table or the CustomerAddressesXref table?2. If it should be the CustomerAddressesXref table should I add an identity column to the CustomerAddressesXref table so that the Sales Order table can use this field as its foreign key into the CustomerAddressesXref table?
-- I apologize, I acutally ment the CustomerAddresses Table. The field should be dependent on the addresses table because the order is specific to the address. At some point you may want to write a query that says "Find all the sales orders to this address with this customer" You will no be able to do that if you base the FK on the CustomerAddressesXref table.
-- The identity column is usually the best way to ensure that there is a unique value to represent each of the fields. You do have a PK on the table that's unique; however, I personally prefer the identity column for visual purposes.
No need to apologize. I just want to get this right. I had actually setup the Sales Order table so that it was dependent on the CustomerAddresses not the CustomerAddressesXref table. For some reason it occurred to me this morning that I would have been better off changing the dependency to the CustomerAddressesXref table. Doing so solved one problem for me, but as you pointed out would have created a bigger problem (not being able to find all the sales orders that went to an address for a specific customer - I hadn't thought of that).
Is there every a reason to make something dependent on a cross reference table?
Thanks so much
October 13, 2009 at 9:43 am
No need to apologize. I just want to get this right. I had actually setup the Sales Order table so that it was dependent on the CustomerAddresses not the CustomerAddressesXref table. For some reason it occurred to me this morning that I would have been better off changing the dependency to the CustomerAddressesXref table. Doing so solved one problem for me, but as you pointed out would have created a bigger problem (not being able to find all the sales orders that went to an address for a specific customer - I hadn't thought of that).
Is there every a reason to make something dependent on a cross reference table?
Thanks so much
I can't think of a reason off hand, I think that in a normalized view there are very few cases where you want to have repetative data. Meaning, if you were to have a customer reference and a customer address reference the following could occur. You have a orders table that references customer_address and customers. Lets say that a business site is bought out by a different customer. You change the customer_id in the customer_addresses table and it references a different client; however, under all the sales orders you now have conflicting repetative information. i.e. the customer_id the sales order references is different than the customer_id the customer_address references. Does this make sense? If you were to reference both of the tables in the orders/sales table the data would not be in normalized form.
I hope this helps!
Thanks,
Bradley Jacques
October 13, 2009 at 9:54 am
Bradley Jacques (10/13/2009)
No need to apologize. I just want to get this right. I had actually setup the Sales Order table so that it was dependent on the CustomerAddresses not the CustomerAddressesXref table. For some reason it occurred to me this morning that I would have been better off changing the dependency to the CustomerAddressesXref table. Doing so solved one problem for me, but as you pointed out would have created a bigger problem (not being able to find all the sales orders that went to an address for a specific customer - I hadn't thought of that).
Is there every a reason to make something dependent on a cross reference table?
Thanks so much
I can't think of a reason off hand, I think that in a normalized view there are very few cases where you want to have repetative data. Meaning, if you were to have a customer reference and a customer address reference the following could occur. You have a orders table that references customer_address and customers. Lets say that a business site is bought out by a different customer. You change the customer_id in the customer_addresses table and it references a different client; however, under all the sales orders you now have conflicting repetative information. i.e. the customer_id the sales order references is different than the customer_id the customer_address references. Does this make sense? If you were to reference both of the tables in the orders/sales table the data would not be in normalized form.
I hope this helps!
Thanks,
Bradley Jacques
I think I just hurt my brain :).
Thanks for all of the help.
October 14, 2009 at 8:20 am
It's possible to do a cascade delete, but to do a cascade update, not so much. You're not actually removing the address value, you're changing which one the customer points to. From a data integrity point of view, nothing is wrong with having other tables pointing to the old address.
You might want to try changing your design. If the key is that you want customer addresses, and these may change, have a third table for relating address to customer. If you then need to hang other tables off that relationship, you can, and it allows you to change internals in the relationship without having to change the tables that refer to that relationship.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 8:46 am
No problem, I'm glad I could clarify, and thanks for the advice grant, I appreciate it. If you want to learn more about data modeling check out amazon for data modeling books. This will help you so much if you work with SQL on a regular basis.
Thanks,
Bradley Jacques
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply