Need help with inventory features

How many item numbers should a POS type system support?  Does anyone have suggestions or opinions on a solid design to support the various possible item numbers, SKU's and UPC codes?  This customer app will be more used in construction type stores and will not involve a lot of barcode scanning or heavy customer flow like walmart.

I'm thinking about the following issues:
(A) An Item can sometimes be ordered from several different vendors, and each vendor may or may not use the same item number
(B) Items can contain other items.  Kits may be necessary.
(C) UPC barcodes need to be able to be attached to each item.  Is this the same as the item number? Always, sometimes, never?
(D) Which number do you recommend using for the primary key?
(E) In the invoicing and purchase order screens, an item should be able to be searched for by any of these possible numbers.  But what number what be considered the selling number, or the normal lookup number?  When using the barcode scanner, how do you make a scanner read a upc number, then translate that to the correct "item number", assuming the two are different?
(F) Do you store qty on hand, qty on order, qty backordered, etc. in the same table?
Who is Participating?
see this tip for your question #F

for part number 50 character fiild will be OK.UPS code should be different filed.Vendor part number will be different

1.table  Inventory - tblInventory
spartNumber -text(50) -primary key
sDescription -text (50)
sUPSCode -text
curPrice -currency
curCost - currency
qtyInstock - number
qtyAllocated -if you are going to use allocation

I will create table to keep other vendors part name as well
tblInventoryVendors(One to many to Inventory)

Think about warehouses if it will be more then one You will need Warehouse table.

You will need Inventory stock table to keep track in and out.

You will need inventory adjustment table as well and if more then two warehouses inventory transfer.

For kits i would have two tables(header and details,actualy for invoices,POS,PO and ... as well)

one more for
Inventory table will be imporpont to have field category adn if you coing to pass it to accounting it should be sales account and cogs account
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ps: the tip includes source code for implementation
HKComputerAuthor Commented:
I checked out the inventory system by allenbrowne.  I can't decide which way is more complicated.  But because of my skills, I did choose to use dmitryz6's recommended method of inventory tracking.  In my system, the only way to change inventory stock levels is through business documents:

Inventory Transfer (Transfer between Bins, warehouse locations etc.)
Inventory Adjust (Adjust for missing, theft, etc.)
Purchase Orders (To Bring Parts In)
Expense Entry (An entry towards an asset or COGS item that increases that items value and decreases stock levels)
Repair Orders (A type of invoice)

I do have to code to loop through these and take appropriate quantities out.  Also, I haven't implemented a solution yet on how to make changes to an invoice or work order without messing up stock levels.  I think nico would recommend a temporary table to hold the values on entry.  I will probably try that method.  I don't understand how to compare the records  in the temp table to the records at the time the user decides to exit the screen.

The only thing I don't understand about AllenBrowne's method is that when you do a "stocktake" and the number is different than what was showing, your inventory levels decrease or increase but you never make a document to say where that inventory did go/could have went.  I think my method may be a little more solid in that respect but perhaps not as robust and requires more code.

I did stumble a little on the how to use a seperate table to hold vendor part numbers.  The theory is fine for reference but when you try to use those numbers as "order as numbers" it can really make things complicated.  A vendor part number needs to be marked as default and only one default is allowed.  And at least one is required.  I've chosen instead  to use these numbers:

ItemID (Autonumber)
ItemNo (Your part number, recommended to use mfg's part number)
UPCNo (It's obvious but this can just include the ItemNo again if no UPC is used)
PackNo (This allows for a corresponding pack number)
OrderAsNo1 (Order this part number as this number from the default vendor)
OrderAsNo2 (Order this part number as this number from vendor number two.)

An ItemNo is Required
ItemNo can be duplicate
ItemNo cannot be changed after it is saved
A Brand/Mfg is required for each ItemNo (Duplicated ItemNo/Brand records are not allowed)

The only hiccup I am running into is figuring out how to import price updates.  It wouldn't be uncommon to get pricing information for a specific brand or vendor and then all items in inventory need to have their cost updated.  Now, do you change the cost "where varItemNo = OrderAsNo1" or "where varItemNo = ItemNo and varBrand = Brand" or "Where varItemNo = ItemNo and varVendor = Vendor"?  Seems this could really get complicated.

So if anyone has more input on the matter, I wouldn't mind hearing it. -HK
My comments:
1. Allen Brownes statement about invenory on hand is absolutely correct. His statement is telling you the same thing every database administrator/programmer (including Dmitry) will tell you.....inventory on hand is a derived(calculated) figure that can be computed and displayed on forms and reports as needed. It is redundant to store the results of such calculation in a table.

2.<<<The only thing I don't understand about AllenBrowne's method is that when you do a "stocktake" and the number is different than what was showing, your inventory levels decrease or increase but you never make a document to say where that inventory did go/could have went>>>>

You are misinterpreting Mr. Browne.  As is the normal practice, adjusments due to inventory counts are documented as an inventory adjustment and stored in an inventory adjustments table like the one Dmitry mentioned.  

3. As to pricing(valuing) the quantity on hand.......again as related to the quantity sold or on hand, are generally derived figures computed pursuant to one of the accepted methodologies: LIFO, FIFO. If a Specific Identification is adopted instead of LIFO or FIFO, then the requirements are different.  As related to the Quantity In (purchases) , pricing is specific to each item purchased.
HKComputerAuthor Commented:
I do appreciate the feedback.  I'm busy thinking...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.