Inventory counting system advise/critique

[rsyCoder]

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,Barcode,Qty
1,C852,123456789,3
2,C852,123456788,1
3,C853,123456789,2


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

EXEC CasesBarcodeCreate 'C853','123456789','1'
 

which would do something like:

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 CasesBarcodeRead '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!