Could you please try to help me in situation with related records. I have system with to main tables: ORDERS and INVOICES. Each has it's own serial number ORDERS::ID.order and INVOICES::ID.invoice and I set the INVOICES::ID.order from ORDERS table to make them related.
In easy deals (daily business) - this is enough, but sometimes I have more then 1 related order for invoice (for instance, when I load a truck with goods from two or more orders). Then I added ORDERS::ID.invoice and made a script to set proper fields. It was OK too.
But I faced the situation when I have 2-3 different invoices shipped under different orders at the same time. In this case my cross-relations can't help, because ORDERS::ID.invoice is overlapped all the time. Seems to be I need to run one more child table for ORDERS to link all invoices it has reference to. My mind is blown and I don't see easy solution (any solution to say truth). Maybe you have? Please help