Design question Order and OrderDetails ShipToAddress Fkeys 2

[AppDev76]

hi,
I'm designing a purchasing system.
i have an orders and order details table.
In the order table I need a ship to address.
I have to options:

1.I can use a foreign key AddressID to reference an address in the Address table. The problem with this is if some one changes the address after an order ha been places and shipped we will loose the info on the ship to address for the order already shipped. In other words, if an old order is referencing an address that has recently been changes and we view that order it will show the new address not the address that was actually shipped to.

2.Insert the address data directly. Denormalize the address.

What is the industry standard for this?

 

[johnherman]

It is standard to store information about an event, such as an order, on the event record itself. For instance, if the order had sales tax, you would want the tax rate and delivery state in effect when the order occurred. It is important to snapshot the event completely, so denormalize.

-------------------------
Sometimes the grass is greener on the other side because there is more manure there !

 

[SQLSister]

What I would do in this case is add the address to the table with the purchase order.

I would take the default value on the form from the table with the customer's mailing address. But when I save the record, it would put it with the order, not update the customer's address. You may also want to specify a way to update the customer address permanently.






Questions about posting. See faq183-874

 

[AppDev76]

Thanks for your replys.
Thats what I thought. To have a snapshot.
It makes a lot of sense.
The only concern that I have is about linking to the same record.
in other words, on an Order, if in the future a use wants to see details about the shipping address, if I save the addressID (instead of the address) I can use the ID to reference the address. If I save the address itself then i would have to query for the address record using the street,state city zip.

johnherman,
can you explain or send me a link that explain in detail the cases for storing information about an event?
and do you think I should do the same for the product line items on an order as well? (I don't think it will appy to the product line items since the product descriptions are readonly)

Thanks

 

[SQLSister]

Yes you should save the product items. If the costs change, then the historical order will change if you just use a link. This could majorly screw up your accounting reports.

Always store all details about an event in time in the table for the event even if it means you denormalize or your data will lose it's integrity.

Questions about posting. See faq183-874

 

[AppDev76]

Thanks SQLSister,
can you refer some resources to me regarding data modeling that will apply to the problem I'm facing now.


Thanks a lot.

 

[AppDev76]

SQLSister,
regarding the product line items...
Is it the standard and norm to save both the productId and product details.
For example, consider Amazon.com, when you view your order details, the item you have purchased is a hyperlink to the product details page. This means that the productID is saved in the Orderdetails table, doesn't it?

Thanks a lot

 

[MDXer]

in PO Systems I have seen there has been a POHead Which keys to customer or vendor with various other columns Then their is a child table which is PODetail containing Line items which with info such a Order QTY, Order Value, Recieve QTY, Recieve Value Shipping Charges etc etc.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

When an event such as an order or invoice occurs, it is important to be able to recreate (reprint for instance) that invoice as it was when the event occurred. Other examples of events include manifests, hospital admissions, and accidents.

Associated with the event, you need to capture all the relevant information. In addition to customer number, you should capture customer name. (What if the customer is bought or merged?), the address (they could move), the pricing and tax info (those can change), credit terms, etc.

You might also want to capture info such as a contact and phone number, but maybe not. The reason you might not want it is that you will certainly not be calling an obsolete contact on a phone number which is not in service. If you are satisfied that product descriptions will not change, or that if they do change, it will not be significant, then I agree you can omit that from the snapshot.

Without a complete list of data elements in your system, I can't tell you what to keep in the snapshot, but if it is printed on the PO/Invoice, it is a strong contender. Using the logic I laid out above will help. If you have a few data elements of which you are unsure, I can help.
------------

-------------------------
Sometimes the grass is greener on the other side because there is more manure there !

 

[AppDev76]

Thanks Johnherman

 

[SQLSister]

We capture product names because products become obsolete or names change extremely frequntly in our business. The customer often knows the name but nothe the part number, so you need this information. Make sure you store the price too as that alos changes and you need the price the customer was charged, not the current price.

Questions about posting. See faq183-874

 

[AppDev76]

Thanks