How Can I Imporve "Shipping" Normalized Table? - Relational Database

Looking at your Shipping record, portions to consider "normalizing" include Item and Destination data.

The ItemHeight, ItemWidth, ItemDepth, and ItemWeight could likely be moved to an Item table and include the ItemID in the shipping record. If these are predictable values, this would work. If these values change for the same item over time, item related fields cannot be normalized to avoid repetition.

The DestinationName, DestinationAddress, DestinationCity, DestinationProvince, and DestinationPostalCode can likely be moved into a Destination table and a DestinationID field added to the shipping record. This would avoid repetitious data. However, be careful, these addresses are not likely attached to a customer record since they must remain the same as a record over time and the customer addresses may change over time. The DestinationID would likely need to change each time a Destination address is added. If Destination is totally unpredictable, then this normalization makes no sense.

Normalizing is all about avoiding duplicate data and values to maintain. It should not be a set of shackles. The concept should serve your needs and make processing more efficient, not chain you to decisions that do not make sense. Some helpful links:
     http://msdn.microsoft.com/en-us/library/aa216117(SQL.80).aspx
     http://databases.about.com/od/specificproducts/a/normalization.htm
     http://en.wikipedia.org/wiki/Database_normalization

Let me know if you have more questions, Eric

Thanks for response. I have some comments,

"ItemHeight, ItemWidth, ItemDepth, and ItemWeight"

This is the size of the box to be shipped, so this most of the time will differ in every shipment since the items vary based on client's request.

"DestinationName, DestinationAddress, DestinationCity, DestinationProvince, and DestinationPostalCode"

It really depends on the client, if the client comes back to us frequently then most probably this client will use same shipping address. Anyhow, customers are expected to come once.

Your response helped.

@rrjegan17:

Well said. Now, i got a new question:

ShippedVia
PickUpLocation
ShippingPrice

I noticed that PickUpLocation and ShippingPrice rely on ShippedVia field. Based on ShippedVia value, either PickUpLocation or ShippingPrice has to be zero. This break 3rd NF.

I though to eliminate PickUpLocation, and then write a short snippet that checks ShippedVia value, if it is "Pick Up" then i will have to find a away to handle it. I'm not sure whether this is a good solution or not.
I was also thinking to isolate PickUpLocation and ShippingPrice their own table. However, either way i will have to do some checking so why create more tables when i will end up doing same thing.

I hope you are getting my point

Thanks! You get the full points. I find your answers are clear and straight to the point. I might have few more DB related questions, so i will be happy if you look at them.

"..Shipping say, Length X Width X Height with some Weight which.."

Weight will always differ

"...I think ShippingPrice relies on Itemweight right..( correct me if I am wrong)..."

Yes, this is correct but ShippingPrice will be stated only when the company (seller) has to ship the item. Otherwise, it will be zero since the buyer will come and pick it up. Either way, i will have to calculate the size of the parcel/package.

Sure..
Just drop me a mail ( in profile) with subject prefixed EE so that I can look into it, if any question needs attention.