Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-05 : 08:11:52
|
| rihardh writes "It might sound foolish but still...In the Northwind sample DB there is a relationship between the Customers (PK) and Orders (FK) tables based on the "CustomerID" field. Beside the relationship and the fact that there is information in the Orders table to which customer this order belongs to (who made the order), there are additional fields in the Orders table which hold the shipping information for that customer (same data as the address, city, postal code,... in the Customers table).Now for the question:If the order gets printed, the developer will probably use the relationship (CustomerID) to retrieve the addres, city and other information for the customer from the Customers table (the shippind address can differ from the original address). What happens if the customers changes locations and there is a need for a address change in the Customers table?For new orders the information is printed correctly (with the new address), but what if you have to print out an older order? The address for the customer (taken from the Customes table based on the relationship) will be the new (changed) address and therefore wrong.Does that mean that additionaly to the relationship (ID) field there have to be fields in the FK table that physicaly hold the same data as the PK table for chronological integrity of data for documents?What happened to data redundancy and normalization?" |
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 08:51:37
|
| Your question taps into exactly what makes database so interesting. As quickly as you determine a database design rule (relational integrity), you can be given a business requirement that invalidates it (gimme the customer address at the time of order) . . .Jay<O>Edit: "I did not have relations between those table. Not one time!!"Edited by - Jay99 on 04/05/2002 08:53:46 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 09:31:23
|
That's a really good point. But, consider the real intention of the Northwind database: it's just an example for people who are new to SQL Server. It's also a standard for demonstrating SQL examples and such. In any case, you're bound to find things about it that you don't agree with  As long as you understand the consequences of setting up foreign keys like this, and the danger of cascades, you can redesign the database to avoid these problems. You can add an OrderShippingInfo table that saves the shipping address independently of the customer info (just be careful, read on!)What you described happened to a coworker of mine; it was actually pretty funny (company moved, some orders were shipped to the old address, then sent back to the shipper, orders were in limbo for two weeks trying to sort them out...WAIT...then the same thing happened to the customer's BILLS!) He was called in on a contract to troubleshoot the (MS Access!) database about this. Not only was there a relationship between Customers and ShippingInfo, he found the cascade updates turned on! And guess which columns were related????? CustomerID AND Address!  So now, NONE of the orders were ever shipped to the customer's old address! (according to the database at least) The guy before him needed to enforce the CustomerID relation, but someone (ok, a dipshit manager) complained "what happens if the customer changes address?" and he inadvertently enforced cascade updates on Address as well! |
|
|
|
|
|
|
|
|
Relations - good or bad?