[RBASE-L] - Re: database design for reports

[Doug Hamilton]

Jim, if I understand it, you've got header and detail tables for the orders.  Do you also do invoicing? I had a similar setup that included invoicing and when items were shipped, I'd create an invoice (header and details) for those items that were shipped.  The invoice header would include the order number and the order table would be updated with the invoice number; given any invoice, I could find the order, given any order, I could find all invoices. If you aren't doing invoicing, you could use those two table as ShipHeader and ShipRows.  Granted, that's two more tables, but, hey, disk is cheap (e.g. ~$100/Tb) and R:Base is fast. Or maybe one table with OrderRowID (unique autonumber ID for each row in OrderRows), OrderNumber, ShipDate QtyShipped & whatever else might be necessary.  When you ship, add rows to this table.  With prudent indexing, use SQL to find what's been shipped and what hasn't. I thought about adding ShipQty, ShipDate etc. columns to your OrderRows table but that gets messy for multiple shipments of an item, unless you don't do partial shipments of an item. ("Never say Never") Any of these would be a significant change to the db and your reports - maybe more than you wanting to do or are budgeted for. It was a number of years ago I did this, so the description is a little sketchy and I haven't thought the above through in great detail, but maybe it'll give you some ideas. Doug Just read your response to Karen - my question about invoicing is answered. Jim Belisle wrote: I am in the process of changing the structure of the database in how we handle or customer orders. In the past, since we seldom had split shipments, we would simple “split” an order by using the same order number (in the header) with an “A” added to the end in essence creating two orders for the same order.   Now I want to keep all items on the same order and split the shipments as needed on the Row level. I am going to use an OrderHeader table and OrderRows table.   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.   Any arguments would be appreciated.   Jim