#StackBounty: #sql #database-design stock management – need suggestion

Bounty: 50

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:

  1. Sheet
  2. Circle (After cutting)
  3. Rolled Circle (After rolling)
  4. Dark Circle (After Buffing)
  5. Ready Circle (After Polish)

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

  1. Cutting
  2. Rolling
  3. Moulding
  4. 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.

  1. Client bought/purchased a product which is calculated as per weight in kg.
  2. 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).
  3. 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

  1. I am unable to manage how much stock I have given to labours
  2. How much remain at their end (i.e. labours end)
  3. 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.

{ id, vendor_id, prod_id, weight, qty, focusOn (weight / qty), 
  rate, amount, remainingWeight, remainingQty }

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

{ id, job_id, prod_id, weight, qty }

focus on parameter check whether to check weight of returned product or qty to make sure accountability.

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.