CODEWITHSUNDEEP
sqlsql-server
mms mms
mms mms

Reputation: 37

based on SQL how to create a proper meaningful tables for stock inventory and for billing system?

I needed to create a stock entry system and billing system..

I have two stock entry tables and one invoice table. I am not issue how far is my invoice table is correct and also the other two table because when a customer purchases a Jewelry stock it should be it is not available in the stock table. Jewelry has an unique stockno because it does not have more than one quantity.

When the customer purchases from Gems by making the invoice. Example the customer purchases No_of_pieces: 20 and the weight: 20.00CTs. Then this should effect the Gems table by reducing.

Can I do all the above methods in the created table below or should I maintain two separate tables for the two stocks entry to show the outstanding.

Gem is one table which has these columns:

ID | Stock_No | No_of_Pieces | Description | Weight | Cost

Jewelry is one table which has these columns:

ID | Stock_No| QTY | Description | No_of_Gems | Cost

In the Invoice table 

ID  | Invoice_No | Stock_No | No_of_Pieces  | Gem_Weight | Customer Name | 
Payment_Mode | Card_Type | Amount | Currency_Type | CT_Amount | Rate | 
Currency_Type1 | CT_Amount1 | Rate1 | Currency_Type2 | CT_Amount2 | Rate2 |
Total_Amount

Upvotes: 2

Views: 554

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

I recommend the Below Design

enter image description here

This is a simple design where you can store multiple Gems / Items on a single Invoice as well as Keep Track of the Stock and Customer Information more efficient way

Upvotes: 1

Related Questions