Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Processing Cash sales


Processing Cash sales

Author
Message
goodguy
goodguy
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1037
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

PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
goodguy
goodguy
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1037
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search