I have a project which contains an order fulfillment module, which handles both cash and credit transactions.
My db has tbl_Invoices_Header, tbl_Invoices_Details, tbl_Payments_Header, and tbl_Payments_Details.
tbl_Invoices_Details relates to tbl_Invoices_Header via FK_Invoice_ID (many items in one invoice);
tbl_Invoices_Header relates to tblPayments_Detail via FK_Invoice_ID (one invoice per payment detail);
tbl_Payments_Details relates to tblPayments_Master via FK_Payment_ID (many invoices per receipt)
In business practice, cash-sales usually double up as invoices and receipts.
In my system, each sale creates records in tbl_Invoices_Header and tbl_Invoices_Details, whether cash or credit.
The tbl_Payment_Details acts only as a junction between tbl_Invoices_Header and tbl_Payments_Header.
In the real world, physical cash-sale receipts show details of products purchased, thus solving the need to refer to an underlying invoice. In RDBMS, this is not easy to implement, as it causes redundancy.
How I can implement the cash-sales processing in my existing db design without complicating the reporting of total sales and receipts by inserting data to multiple tables?
Any assistance will be appreciated.
Experience is a bad teacher whose exams precede its lessons