Schema Design Question - Should I put [Ship Date] in Orders_Table or Order_Detail_Table ?

[Will]

Since a complete order may be filled & shipped on diferent days and in
different boxes... and from different warehouses and shipped by different
shipping companies...

It is looking like we should put a lot of data into the Order_Detail_Table
instead of the Orders_Table...

Ship_Date
Received_Date
Container_Number (We may ship an order in multiple boxes - use this to
produce packing list)
Shipper_ID (ID of shipping company used)
Tracking_Number (Shipping company assigns each box shipped a different
Tracking Number)
Paid_Amount (Cust may not receive all boxes for order & pays only
for items received)

Do you guys concur that putting all this info into each line item in the
Order_Details_Table is the way to go?

FINALLY - ON PRICE: - How do you treat [Price_Charged] as opposed to
[List_Price]

Many of our customers are wholesalers and get a discount. We could
'Calculate' the [Price_Charged] but if their discount ever changes it would
mess up all older records.

thanks for any help on this.

 

[Mike Labosh]

Since a complete order may be filled & shipped on different days and in

different boxes... and from different warehouses and shipped by different
shipping companies...

It is looking like we should put a lot of data into the Order_Detail_Table
instead of the Orders_Table...

I have come across this issue before. The decision is based on the business
requirements:

1. An order must not be shipped until all its items are available to be
shipped all together at once: Put OrderDate in the Orders table.

2. A partial order is available and one or more items are not available or
backordered or something: Put OrderDate in the OrderDetails table

FINALLY - ON PRICE: - How do you treat [Price_Charged] as opposed to
[List_Price]

Many of our customers are wholesalers and get a discount. We could
'Calculate' the [Price_Charged] but if their discount ever changes it
would mess up all older records.

Again, this sort of comes down to a combination of business requirements and
customer status. If a customer's discount has a possibility of changing
periodically, then their current discount value should be a column in your
Customers table.

Sometimes, you may get a 501C3 not-profit customer, in which case you cannot
charge them sales tax.

Sometimes, your company might put a certain product "on sale", so that item
may be discounted for everyone, not just specific customers.

I feel a meeting coming on, between you, your users, and your bosses.
--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[Rob Wilson]

Since your line items will have at least one corresponding transaction
involving shipment data for each line it makes sense to add it to the
detail table, but if your system allows for individual lines to be
partially shipped then you will have instances where multiple shipment
transactions/data can occur for each line. For this reason I suggest
you creat a separate shipment transaction/data table referenced to the
detail table by order/line number and differentiated by date/time
stamp.

In our system we have a standard price and preferred pricing schedules.
The key for us is that the price given is recorded in the sales order
detail (by line item) and no where else. This way any item can be any
price at any time for any customer and doesn't impact prior postings.

Hope I have helped

Rob Wilson.

 

[Rob Wilson]

Since your line items will have at least one corresponding transaction
involving shipment data for each line it makes sense to add it to the
detail table, but if your system allows for individual lines to be
partially shipped then you will have instances where multiple shipment
transactions/data can occur for each line. For this reason I suggest
you creat a separate shipment transaction/data table referenced to the
detail table by order/line number and differentiated by date/time
stamp.

In our system we have a standard price and preferred pricing schedules.
The key for us is that the price given is recorded in the sales order
detail (by line item) and no where else. This way any item can be any
price at any time for any customer and doesn't impact prior postings.

Hope I have helped

Rob Wilson.

 

[Harvey Thompson]

Since a complete order may be filled & shipped on diferent days and in
different boxes... and from different warehouses and shipped by
different shipping companies...

It is looking like we should put a lot of data into the
Order_Detail_Table instead of the Orders_Table...

Ship_Date
Received_Date
Container_Number (We may ship an order in multiple boxes - use this
to produce packing list)
Shipper_ID (ID of shipping company used)
Tracking_Number (Shipping company assigns each box shipped a
different Tracking Number)
Paid_Amount (Cust may not receive all boxes for order &
pays only for items received)

Do you guys concur that putting all this info into each line item in
the Order_Details_Table is the way to go?

FINALLY - ON PRICE: - How do you treat [Price_Charged] as opposed to
[List_Price]

Many of our customers are wholesalers and get a discount. We could
'Calculate' the [Price_Charged] but if their discount ever changes it
would mess up all older records.

thanks for any help on this.

Will,

Neither, the fields you have listed are not attributes of an order or an
order item; they're attributes of a shipment. They belong in the
Shipments_Table.
This assumes that one shipment can consist of many order items, but any
order item is shipped as whole. If partial shipments are possible, the
relationship is many to many and you need a junction table.

 

[Will]

Rob,

Thanks for the help.

You said...

"creat a separate shipment transaction/data table referenced to the detail
table by order/line number and differentiated by date/time vstamp."

I was going to create...

tblContainer
idContainer
Ship_Date
Shipper
Tracking_Number
Ship_Method
Ship_Weight
Ship_Cost
Received_Date
Etc

Then put idContainer in the Order_Items table like this...

tblOrderItems
idOrder
idProduct
idContiner

Alternately I considered linking/relating...
tblOrders to tblContainer to tblOrder_Items to tblProducts

Like this...

tblOrders
idOrder

tblContainer
idContainer
idOrder

tblOrder_Items
idContainer
idProduct

tblProducts
idProduct

Is either of these what you were talking about?

AND ON PRICE...

How can I have a field in a table for the 'calculated' extended price
where...
Extended Price = List Price * Discount

and make sure if we change the discount... all the previous 'calculated'
fields won't change.

and, how do you manage sale prices or one time specials or whatever?

thanks for all the help.

Will

 

[Rob Wilson]

For me your first example is the most logical. On the pricing... In
our system the only time pricing is calculated is at the time the order
is entered and the calculation only for that line on that order at that
time. There is no global pricing calculations done at all.

Rob Wilson.

 

[Will]

Rob,

You said...

"In our system the only time pricing is calculated is at the time the order
is entered and the calculation only for that line on that order at that
time."

How do you do a calculation like...

Net Price = List Price * Discount

Such that if List price or Discount ever change it won't effect previous
entries?

Do you use an "Update" query for only that record or what?

Thank you for all your kind assistance on this.

Will