stock management - need suggestion I am trying to make a stock management software according to my client's requirement, where I bought products by weight or qty depending on product type. Which has following database structure.
User { id, name, address, role (Customer, Vendor, Labor etc)}
Product {id, name, type}

Types of product:

Sheet
Circle (After cutting)
Rolled Circle (After rolling)
Dark Circle (After Buffing)
Ready Circle (After Polish)

The issue is the product I purchase may go through 4 process:

Cutting
Rolling
Moulding
Polish

The above process depends on what type of product I purchase. If I purchase raw product then I may give it to labour who will cut it and give it back to me (i.e. cutting). In this process some material goes to scrap.
As you can see if I buy sheet which is purchased on weight is gone for cutting and turned circle (product type 2). Product type 2 is calculated as per qty.
I can ask client to put approximate weight of qty they received but issue is how can I manage the stock how much is remain at labour place.

Consider I am distributor I purchase products from multiple vendors, the products are classified in several types which is mentioned above. Every vendor has his price for products.
After purchase the product may go for processing or it may be sold. If it goes to process there will be 4 stages, according to client he can decide to sell product during any stage.
For e.g.

Client bought/purchased a product which is calculated as per weight in kg.
The product is given for cutting (stage 1) to multiple labours in parts for e.g. If I have 100 kg raw material, I gave 50kg to labour1, 20kg to labour2 and remaining is given to labour3, during this stage the product turned into multiple pieces (number of pcs are not same every time due to scrap generated during this process is not fixed so I cannot calculate the number of pcs by formula as of now we can expect the approximate pcs which may vary +/- 10 to 100).
Now labour will send me the pcs in parts for e.g. Labour1 got 50kg raw material which turned 1000 pcs he sent me these pcs in parts i.e.  500 pcs were sent day before yesterday 200 pcs were sent on yesterday 100 pcs were sent today Rest pcs are remaining at labour1 side

Issues I am facing

I am unable to manage how much stock I have given to labours
How much remain at their end (i.e. labours end)
How to maintain stock details for selling purpose at it is not managed through one table.

What I have done till now
I created following schema to solve my issue but I am not sure how much it is correct according to rules of DBMS.
Purchase 
{ id, vendor_id, prod_id, weight, qty, focusOn (weight / qty), 
  rate, amount, remainingWeight, remainingQty }

Job 
{ id, purchase_id, prod_id, labour_id, weight, qty, focusOn (weight / qty) }

Returns 
{ id, job_id, prod_id, weight, qty }

focus on parameter check whether to check weight of returned product or qty to make sure accountability.
You could approach it as double-entry book keeping. Each user has an "account" for each product, to which you credit and debit transfers. You act as the "bank" in the middle of the network. Each account's balance is the stock, either on-hand for sale or out for work.

The tables would be something like:

Participant
  ParticipantId
  &lt;other columns&gt;


I specifically do not call this "user" since participants need not be people. You will have a participant record, too.

Product
  ProductId
  UnitOfMeasure    -- will be "kilogram" or "piece"/"quantity"


One row for each of the five you mention, sheet through ready circle.

Account
  AccountId
  ParticipantId
  ProductIt


Each participant needs an account per product since there is no direct translation from e.g. sheet to circle, according to your description. Even from circle to rolled circle there may be wastage during handling so sending out 100 units does not mean 100 units are always returned. It's a lot like owning some dollars and some Yen - you can convert each to the other, but the point-in-time exchange rate fluctuates. Each must be held in its own bank account.

You yourself will have an account for each of the five products you deal with. This represents the items sitting on your shelves at any point in time, waiting for further work or to be sold.

Transfer
  TransferId
  FromAccountId
  ToAccountId
  &lt;probably date, time, recipient etc.&gt;
  Quantity


A row here records the movement of a given quantity of a particular product from one recipient to another. You know the participant and the product from the corresponding account. From the product you can determine if the Quantity represents a kilogram or an item.

For ease of processing you'll probably want to duplicate this under each account. This will need another table:

AccountTransaction
  AccountTransactionId
  AccountId
  TransferId
  DebitAmount
  CreditAmount


For each Transfer there will be two rows in AccountTransaction - one each for the FromAccount and the ToAccount; one will be a debit and the other a credit.

Over the years this table may get large and queries sluggish. At intervals (annual accounts?) it may be worth rolling it forward to a new starting balance and archiving previous transactions to a different table.

To answer your questions:


  I am unable to manage how much stock I have given to labours


Sum that Participant's AccountTransactions.


  How much remain at their end (i.e. labours end)


Well, you know how much you've transferred (previous question) but since you've no strong conversion rate or wastage you cannot know how much is actually in their workshop and how much has been scrapped. For that the labourers would have to report scrap and wastage directly.


  How to maintain stock details for selling purpose at it is not managed through one table.


Sum the AccountTransaction tables for your participant ID - these accounts record the units assigned to you and, presumably, sitting in your warehouse.



Apology: it is a while since I last wrote accounting software. Many of the details are a bit fuzzy now. I don't have the capacity to revise all the details and type a fully polished answer. There are a great many online resources available which can explain it more fully. Some finessing may be require around the foreign keys in Transfer and AccountBalance. Hopefully this is sufficiently clear to point you in the right direction. Good luck.