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?