stock management - need suggestion

Question

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.

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.

Answer 1

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
  <other columns>

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
  <probably date, time, recipient etc.>
  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.

Answer 2

(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).

Scrap generated is not fix becasue of product quality or dimension of piece is not fix.

Due to this reason it is difficult to determine,

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

Product Type

TypeID
ProductType


Product Process

ProcessID
ProcessName

Product Master

ProductID
ProductName


ProductVendorMapping

ProductID
VendorID
TypeID
Qty
UoMID
Cost

ProductStage

PStageID
ProductID
ProcessID
ProcessDate

As soon as any product is purchase this table will have entry according to stage of that product.Thereafter it will keep updating after finish each stage.

ProductTaskName

TaskID
Taskname 
ProductID
TaskDate

Suppose there is fresh arrival of product 1 which is to be distributed among labours for processing.

Say after 1 month this task is over and fresh arrival of same product is to be process.

Then how will user and system distinguish between these two task because productid and labourid is same.

so before deploying to labour Create new task before assinging to labour.

ProductLabourMapping

ProductLabour id int identity(1,1)
ProductID
LabourID
TaskID
QtyLentToLabour
UoMID
StartDate
PieceDimension (to be discuss)
PieceReturn int nullable
ReturnDate nullable
ScrapWeight nullable
ScrapUOMID nullable
isComplete bit default(0)

Since there is no fix formula (as you say) you have to manually record each transaction between labour and system

Labour1 get 50 kg of product 1. So new entry in this table. Thereafter 500 pcs were sent on day 1.so update same task.

As soon as all labour has completed same task,Iscomplete =1. At this stage productstage table can updated.

ProductTransaction

ProductID
CustomerID
TransactionDate
Cost
ETC

This table record the sell of product.

When raw product is given to labours then how will you sell it ?