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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
see this tip for your question #F


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.