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

[Doug Hamilton]

Oops, you're right, my error. The Order Detail table gets the invoice #, not the Order Header. But even that would cause problems if there were multiple (partial) shipments for an item resulting in multiple invoices. Maybe the invoice number isn't even needed in the Order Detail; I did have the order number in the invoice header table, so it could done with a view - I'd have to look at that old db, it's been years. Can I go get my coffee now?  :) D karentel...@cs.com wrote: Doug:  How could you update the Order table with the invoice# if there's the possibility of >1 invoice per order?   Wouldn't you rather put the order# in the invoice table? Karen 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