Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Stock Management design for Mysql

    hi guys,,1st im sorry for my bad english
    i am new in this forum and also to database.I know the basics, but my design skill not too good,,now i want to make a mini ERP program for my last assignment .and i use my sql for the database..
    now i get stuck for the stock management here,this table product is related to my manufacturing order and purchase order but i dont attach it because i think made it correctly,
    but do u think this design for the product is working??
    i made product and product_detail because i want to know history of the stock in spesific per month or year,, eg. i want to know the history for product "MOUSE" from 1 june to 2 july..
    now i think this is not working good, because i got trouble if there is any mistakes values from purchase order / manufacturing order from past month.. can u give me advice to make better design??
    i hope u can understand my bad English

    this is the product and product_detail relation attachment

    Click image for larger version. 

Name:	product.png 
Views:	4 
Size:	9.1 KB 
ID:	14703

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    in an ideal world stock control is not a function of a specific product
    you define products
    you define stock movements and form that you can calculate what your current stick levels is. sometimes its possibel to hvae a negative stock (especially if you have forward committments for that stock item (ie you have committed to use 100 of product X for use in manufacturing ovver the next 3 months so you know you have the reuirment but you dont' have sufficient stock on hand

    in pricniple you should always calcualte stock on hand as and when required and not store such derived values. however soemtimes for perfomrance reasons you may want to to store a stock on hand level.

    the key to a stocxk system is a list of transactions reflecting actual stock movements. in the past Ive defiend a transactions type table with an affect value (either +1 or -1) which indicates what the affect of the transation is... so +1 * qty indicates stock has risen (say through purchases, stock count adjustment, return from customer), -1 * qty equates to a reduction in stock through sales, transfers, scrap or so on.

    from that its very easy to get to a stock level
    each transaction code has an affect multiply that by the qty in the transaction itself, summed over time gives you the free stock on hand.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •