May 22, 2010 at 2:42 am
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.
May 22, 2010 at 6:26 pm
goodguy (5/22/2010)
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.
Let me break the news for you 🙂 it is easy to implement and causes no redundancy at all 😉
Do not confuse what you see printed in a piece of paper with the underlying data model. A proper ER model would include what finally will be implement as a ReceiptTable (1 row per receipt), a InvoiceTable (1 row per invoice) and a ProductSoldTable (1 row per product sold per transaction).
Would you like to work on the relationships?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 23, 2010 at 2:10 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply