Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Processing Cash sales Expand / Collapse
Author
Message
Posted Saturday, May 22, 2010 2:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
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.



Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Post #926389
Posted Saturday, May 22, 2010 6:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #926466
Posted Sunday, May 23, 2010 2:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
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

Post #926491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse