Larry,
Thanks for your input. We like to track the open orders and at present we do that by the shipdate on the header (WHERE shipdate is NULL). If the open orders is based on the detail (Order rows), I want to make sure the header information is not constantly duplicated with each row of data. I only want one instance of the header per order regardless of the number of rows. At present I use a view connecting the header with the detail. With your suggestion, I assume I would use the Order rows table as the primary table for this report (with open orders based on the backorder amount or remaining amount) and the header info would be brought in via lookup variables. Correct me if I am wrong. I know this is a basic question, but most of my NEW reports are just copies of existing ones so my memory fails me sometimes when starting from scratch. Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Thursday, December 17, 2009 9:13 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: database design for reports << My two choices are these: 1) Do not put the ship date in the OrderHeader until the order is completely shipped. This would be easiest to implement since the reports would have little changes to them. 2) Put the Ship date in the OrderHeader but have the report based on the backorder amount in the OrderRows table. This would involve more changes in my present reports but may be the best way to go. >> With split shipments, the ship date is no longer an attribute of the Order, but rather of the OrderRow. If the _only_ thing you track about the actual shipment is the date it shipped, then you should migrate the ship date column from the header to the detail table. If you track additional information about the split shipment (for instance, tracking number) then you should create a new table called ShippingHeader and put the shipping information (include the shipment date) there. Then, in the OrderRow table keep the column for OrderID that links back to the order header but add a new column ShippingID that links to the ShippingHeader table. -- Larr
