My application needs to implement a one to one relation between multiple tables. I have a table which store companies (which can be customers and suppliers, or both). There are twi Bit fields, Customer and Supplier.
Then I have different tables for various operations: Invoices, Bank operations, Cashdesk operations. An I need to pair payments with invoices. A payment is not exact amount of an invoice, but it can be split over each number of invoices. Also, an invoice can be split over multiple payments. Payments can be from both bank or cashdesk operations
My original approach was to have a table, PaymentRelations, with Foreign Keys InvoiceID, BankOpID, CashOpID and Amount, and for any payment between between them, I create a record with only two foreign ID's filled, and the corresponding amount. This way in any moment I can know for each operation (invoice or payment) how much was paid.
Also there are RI requirements, so if a document is involved in payment relation, it cannot be deleted (or there is cascade delete, so if a payment of invoice document is deleted, the related PaymentRelations records are deleted, so the counterpart operations are freed - they are no longer involved into payment relations so their amount can be fully used into other payment relations).
But appeared another situation. Since partners can be both customers and suppliers, it is possible to compensate between same type of operation on customer and supplier side of the same partner (e.g. a partner is both customer and supplier, he made an invoice as supplier for 100 and received an invoice as customer for 150, 50 was compensated between the received and the sent invoice and the rest of each is paid through one or multiple payment operations). This can also happen for the other operations (e.g. he paid through a bank operation 100, he received through another bank operation 200, and 50 needs to be compensated between those two operations; same apply for caskdesk operations).
What approach would you use to model this kind of relations?
