Referential Integrity - How to handle the following sitiuation

Question

Referential Integrity - How to handle the following sitiuation

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Al-279884 Old Hand Points: 383 More actions · Answer 1

John Rowan (10/15/2009)
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.

...I am thinking of my Amazon account. I have multiple shipping/billing addresses and I have to pick one when I place my order. If that is the case than probably an Order needs to be linked to a Customer/Address combo, not just a Customer...

meichner SSCertifiable Points: 5563 More actions · Answer 2

Al-279884 (10/15/2009)
John Rowan (10/15/2009)
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.
...I am thinking of my Amazon account. I have multiple shipping/billing addresses and I have to pick one when I place my order. If that is the case than probably an Order needs to be linked to a Customer/Address combo, not just a Customer...

Your analogy is a good one. Using your line of thinking you place an order for a book at Amazon. On that order you selected one of several shipping and billing addresses that you use. The order is placed. It has

you as the Customer and your Shipping Address and Billing address is your house. Just before the Order is shipped to you, you contact Amazon and tell them you have moved. Amazon has to make sure that the book and the bill go to your new address. That is what I am trying to design. Any ideas?

Thanks

Al-279884 Old Hand Points: 383 More actions · Answer 3

meichner (10/15/2009)
Al-279884 (10/15/2009)
John Rowan (10/15/2009)
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.
...I am thinking of my Amazon account. I have multiple shipping/billing addresses and I have to pick one when I place my order. If that is the case than probably an Order needs to be linked to a Customer/Address combo, not just a Customer...
Your analogy is a good one. Using your line of thinking you place an order for a book at Amazon. That order has you as the Customer and your Shipping Address and Billing address is your current location. Just before the Order is shipped to you, you contact Amazon and tell them you have moved. Amazon has to make sure that the book and the bill go to your new address. That is what I am trying to design. Any ideas?
Thanks

My idea is a couple of posts above. Your Orders table should have a CustomerID, ShippingAddressID, BillingAddressID columns. ShippingAddressID and BillingAddressID columns are foreign keys to Address table. "Other" tables, let say ShippingTracking, should have a OrderID column, foreign key to Orders table. Address changes are made by a simple update in Orders table, ShippingAddressID/BillingAddressID columns.

meichner SSCertifiable Points: 5563 More actions · Answer 4

Al-279884 (10/15/2009)
meichner (10/15/2009)
Al-279884 (10/15/2009)
John Rowan (10/15/2009)
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.
...I am thinking of my Amazon account. I have multiple shipping/billing addresses and I have to pick one when I place my order. If that is the case than probably an Order needs to be linked to a Customer/Address combo, not just a Customer...
Your analogy is a good one. Using your line of thinking you place an order for a book at Amazon. That order has you as the Customer and your Shipping Address and Billing address is your current location. Just before the Order is shipped to you, you contact Amazon and tell them you have moved. Amazon has to make sure that the book and the bill go to your new address. That is what I am trying to design. Any ideas?
Thanks
My idea is a couple of posts above. Your Orders table should have a CustomerID, ShippingAddressID, BillingAddressID columns. ShippingAddressID and BillingAddressID columns are foreign keys to Address table. "Other" tables, let say ShippingTracking, should have a OrderID column, foreign key to Orders table. Address changes are made by a simple update in Orders table, ShippingAddressID/BillingAddressID columns.

Before I continue I would like to thank the both of you. I really appreciate the time and effort you both have put into explaining this stuff to me. While I don't have a lot of background in design, I am always looking to improve the way I do things.

Some where along the line I must have confused things. In an effort to simplify the post I left out what I thought was extraneous information. At this point I think it best to list the tables that I have been using in my application. I apologize for any confusion.

Customers

Primary Key is CustomerID

Addresses

Primary Key is AddressID

AddressType

Primary Key is TypeID (types are billings, shipping...)

CustomerAddressesXref

Primary Key is CustomerID + TypeID + AddressID

Orders

Primary Key is OrderID

Foreign Key is CustomerID From the Customer table

Foreign Key is AddressID From the Addresses table

Invoices

Primary Key is InvoiceID

Foreign Key is CustomerID From the Customer table

Foreign Key is AddressID From the Addresses table

Some Future table that I don't know about yet

Primary Key is ID

Foreign Key is CustomerID From the Customer table

Foreign Key is AddressID From the Addresses table

I have been told that I should never have a dependency on the CustomerAddressesXref table as it would make lookups like 'Give me a list of all Customers at a specific address' very hard to do.

So given this information along with the problem that I am trying to rectify, how should I proceed?

Thanks again for the time.

Al-279884 Old Hand Points: 383 More actions · Answer 5

Invoices table would need an OrderID column, foreign key to Orders.OrderID. Otherwise, how do you know what are you billing for?

I would not put CustomerID and AddressID on the Invoices table, you have that relation established in the Orders table. To retrieve the customer name and mailing address for an invoice you will have to join Invoices to Orders by orderID and then Orders to Customer by CustomerID and Orders to address by addressID.

I do not know if this would be an universal rule for all the other tables that you do not know yet. For instance, if this other table would be used to store tracking information for a given order, than you create a foreign key to Orders and do not store customer or address. But if the other table would be PreferedShippingMethod, then it is related directly to Customer/Address entity and should have a CustomerID and AddressID column, foreign keys to respective tables.

I agree that generally you should not build foreign key constraints pointing to CustomerAddressesXref table, but nobody knows extacly your application requirements may be in the future. What if you were asked to store customer address changes historically? And I do not think that "Give me a list of all customers at a specific address" query would be very hard in such a case; it would simply require an additional join.

I would also suggest that you do some google/bing searches on this topic. The shopping/billing/shipping scenario is used very often as a working example in articles and tutorials for both application development and database design.

meichner SSCertifiable Points: 5563 More actions · Answer 6

Al-279884 (10/16/2009)
Invoices table would need an OrderID column, foreign key to Orders.OrderID. Otherwise, how do you know what are you billing for?
I would not put CustomerID and AddressID on the Invoices table, you have that relation established in the Orders table. To retrieve the customer name and mailing address for an invoice you will have to join Invoices to Orders by orderID and then Orders to Customer by CustomerID and Orders to address by addressID.
I do not know if this would be an universal rule for all the other tables that you do not know yet. For instance, if this other table would be used to store tracking information for a given order, than you create a foreign key to Orders and do not store customer or address. But if the other table would be PreferedShippingMethod, then it is related directly to Customer/Address entity and should have a CustomerID and AddressID column, foreign keys to respective tables.
I agree that generally you should not build foreign key constraints pointing to CustomerAddressesXref table, but nobody knows extacly your application requirements may be in the future. What if you were asked to store customer address changes historically? And I do not think that "Give me a list of all customers at a specific address" query would be very hard in such a case; it would simply require an additional join.
I would also suggest that you do some google/bing searches on this topic. The shopping/billing/shipping scenario is used very often as a working example in articles and tutorials for both application development and database design.

I want to thank both you and John R for all the help you have given me. I know how busy this industry keeps us. So I do appreciate all of the time the both of you devoted to helping me out. After this experience I think I need to find a good data modeling book.

Thanks again