locked
Help about designing Database RRS feed

  • Question

  • hi there , what is the best way to make 

    Table called Materials(MaterialID,MaterialName,CurrentQuantity)

    another Table called MaterialWithdraw(WithdrawID,WithdrawDate,WithdrawQuantity)

    my point here is make something like Balance and we can take from and we can add to as refund , i made this once but as trigger when information inserted to MaterialWithdraw table added a trigger update the Materials table by MaterialID to add or subtract the quantity , but i i send here because maybe professional programmers and database designer have a batter and easy ideas about this project 

    thank you helpers

     


    Hesham Hemdan Saleh

    Friday, June 1, 2012 3:46 PM

Answers

  • Do you update CurrentQuantity with WithdrawQuantity value ?

    1)As Brian proposed use a stored procedure to update this value

    2) Is that possible to have MaterialID column in MaterialWithdraw and then take the balance from MaterialWithdraw table?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 6:59 AM
  • ... and while creating that stored procedure as suggested by Brian and Uri, it'll be a good idea to use TRANSACTIONs so that any error does not messes up the inventory.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Sunday, June 3, 2012 7:22 AM

All replies

  • TRIGGERs hide code and can be hard to work with.

    If there is a set of rules to be followed for a specific operation, use a stored PROCEDURE. The SP can implement any rules explicitly. If required, you can REVOKE DML rights from the TABLE fording the SP to be used.

    • Proposed as answer by Reddy Balaji C Saturday, June 2, 2012 11:26 AM
    • Unproposed as answer by HESHAM HEMDAN Sunday, June 3, 2012 9:45 AM
    Friday, June 1, 2012 4:26 PM
    Answerer
  • Do you update CurrentQuantity with WithdrawQuantity value ?

    1)As Brian proposed use a stored procedure to update this value

    2) Is that possible to have MaterialID column in MaterialWithdraw and then take the balance from MaterialWithdraw table?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 3, 2012 6:59 AM
  • ... and while creating that stored procedure as suggested by Brian and Uri, it'll be a good idea to use TRANSACTIONs so that any error does not messes up the inventory.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Sunday, June 3, 2012 7:22 AM