What is best for the Stock and invoice DB

[rehanemis]

Hi Experts,

I need to create a small scale program so that I can maintain stock and can create invoice also.

I need only suggestions from you as:

1. Is it better to move the product from Items Purchased (items Stock) Table to Item sold Table when the item is sold? or any other idea?

2. When I sold items to a customer and the customer returned back a item which is sale return then where to store this item? in a separate table of items "Sale return" or just to put that item in Item Purchased Table with a flag that this item is returned back S.R (Sale Return)?

3. When I want to return the item to company (Purchase Return) then where to store this item ? in Separate Table of "Purchase Return" Or just to put a flag in Item Purchase Table (P.R= Purchase Return).

Please share best idea so that I don't face problem in future to handle the stock, purchase return, sale return and sale.

Waiting of your kind reply.

 

[jdraw]

You may get some ideas on Stock Control/management from this free youtube video.

For info on database design and planning see the links on this post.

Good luck.

 

[Minty]

Following on from JDraws advice, Stock control is not a trivial task particularly if you start needing serial number (individual) item tracking.

Your initial ideas are not likely to work - you need to think of stock movements as transactions from the same table and mark them as + (Stock in) or - (Stock out) so that you can always get an accurate calculation of your current stock. You should NOT store the current balance.

 

[rehanemis]

So you means that Setup up flag is best?

 

[Minty]

So you means that Setup up flag is best?

I'm sorry I don't know where or what Setup up flag is?

This example http://allenbrowne.com/AppInventory.html may assist you with a start - please read the explanation of the issue on the page, and it should hopefully point you in the right direction.

 

[rehanemis]

Sorry,

Actually I need to just got idea of creating tables. My work process is very simple.

1. I purchased items from suppliers, So I want to record them in a table.
2. I want to sale item and want to reduce that item from stock.
3. Want to maintain Purchase return and sale return.

Please guide me that separate tables for each one are best or what is your idea?

 

[Minty]

Please have a look at the sample from Allen Browne that I linked to. It has a table layout that covers stock and invoicing.

Simply put you have suppliers, stock_Movements , orders and customers.
Stock comes IN from suppliers. Stock goes OUT to customers via an order.

Returns come back IN from customers or Return stock go back OUT to a supplier.

All of these are transactions in one table that you simply store a transaction type along with the unique part number and qty In or OUT