• Thank you, Paul. I am honoured to get a reply from an author-grade pro. Of course I want any help to map the relationships.

    After racking my brains yesterday, I have concluded that my design has a fundamental flaw in that I was recording sales details as invoices, so that goes out the door. My problem is that, as a db/reporting practitioner, I find unacceptable the accounting practice of omitting buyers' details from cash sales records.

    I am now considering this design:

    1. Record all sales details under tbl_Sales_Detail, which relates to tbl_Sales_Header through FK_Sales_ID.

    2. If an optional field FK_Customer_ID in tbl_Sales_Header is populated, then a trigger will generate a record in tbl_Invoices, which ties to tbl_Sales_Header via FK_Sales_ID.

    3. Payments will be saved to tbl_Payments_Master, and the sales being settled will be saved to tbl_Payments_Details via FK_Sales_ID.

    4. Invoice_ID will NOT be saved to tbl_Payments_Details, but credit sales details will be retrieved via a View connecting Clients, Sales and Payments tables.

    I shall be grateful if you could help with fine-tuning this scenario.


    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons