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

[Doug Hamilton]

Thanks, Karen - I got the coffee. Partial shipments is why I mentioned a third table: 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. You can add any number of rows for a specific item, each with a ship date and qty. In spite of the coffee, I still haven't thought this through all the way. OT from my son:  An SQL query walks into a bar, approaches a couple of tables and asks "May I join you?" Doug karentel...@cs.com wrote: Doug:  yes, I think you SHOULD get your coffee now. And you bring up the other problem I had when I was trying to figure this out myself a few years ago.  You could ship partial quantities of an item.   So one item could have multiple invoice#s / tracking info. That's what made my head spin and I never implemented a more accurate shipping record. Karen 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