Inventory counting system advise/critique

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • rsyCoder
    New Member
    • Jun 2010
    • 7

    #1

    Inventory counting system advise/critique

    I am faced with a decision about a design I would like to get right the first time. So a straight up answer or any helpful advice will be well appreciated.

    We are going to do an inventory. The files that will be created for the inventory will be simply a column of "barcode". There are still a lot of products that need to be barcoded and I was hoping to save time during the actual counting by creating sealed "Cases" for product that is back stock and will most likely not move between now and counting. This is where my decision comes in. What is the best design for such a task? Below is the option I have considered but I will take any advise that seems like the subjective "right" way. Also where any short comings may be with this option and the benefits of your suggested options would be pretty important in making my decision (and anyone else reading this and having to make a similar decision).


    I would have a table named something clever, like "Cases":


    id,CaseNum,Barc ode,Qty
    1,C852,12345678 9,3
    2,C852,12345678 8,1
    3,C853,12345678 9,2


    then use PROCs like the following for CRUD(Create,Rea d,Update,Delete ):

    Code:
    EXEC CasesBarcodeCreate 'C853','123456789','1'
    which would do something like:



    Code:
    IF EXISTS (SELECT id FROM Cases WHERE CaseNum = @CASENUM AND Barcode = @BARCODE)
    BEGIN
    	UPDATE Cases
    	SET qty = qty + @QTY
    	WHERE CaseNum = @CASENUM AND Barcode = @BARCODE 
    END
    ELSE
    BEGIN
    	INSERT Cases (CaseNum, Barcode, Qty) Values (@CASENUM, @BARCODE, @QTY)
    END


    This is going to be a very SQL centric method where most of the logic will be done by these SQL Server PROCs and the program I write around it will pretty much just parse files which will look like:

    123456789
    123456788
    C852
    123456787
    C853


    So the parser sees that the line C852 starts with C and goes to the db With EXEC CasesBarcodeRea d 'C852' that will return:
    123456789 3
    123456788 1

    the program will parse and replace C852 with:

    123456789
    123456789
    123456789
    123456788

    Leaving file data with:


    123456789
    123456788
    123456789
    123456789
    123456789
    123456788
    123456787
    C853

    and moving on.

    <SUB QUESTION>
    Is there a way I can query the aforementioned table to get back the barcodes in a recordset like:

    123456789
    123456789
    123456789
    123456788

    Or is that going to be a lot of looping (some cases may have hundreds of products) that will be best left to the software?

    </SUB QUESTION>


    To me this seems like a workable solution. I would like it to become more once the initial count is complete and spread it out to the Bin system and people can add and remove items from the cases/bins and search for what cases have a certain item and how many. General inventory management system things. But would like to get it right the first time around so we don't do a lot of work that ends up being useless because of an over site here.

    I would also like to hear any horror stories that I can keep in mind from people who have already been down this path.

    Thanks in advance!
Working...