how to use Barcode in POS and Inventory system (Design wise)?
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
First step is to investigate "bar code". These days there are many variations. You probably mean a UPC code which is a specific form and type of bar code. I suspect that you probably don't want it as the primary key because it might need to be changed. You might want to also consider what happens in the following scenarios. 1. The cashier must manually enter the code but they enter it wrong. How does this impact inventory? How is a correction handled? (Think of fraud control in terms of this.) What happens to returns? What happens to returns if it was corrected before the return happened? 2. The cashier scans a item but the UPC code is not found in the database - now what?
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
Omar Rwemi wrote:
I mean should I simply just read the barcode
Yes. Most scanners will input the code as if it were entered by the keyboard.
Omar Rwemi wrote:
and use the Part number as an ID (Primary key) for the item, or is there a better way?
Yes. Add an identity-field (or a guid) as a primary key, and put an index on the part number :)
Bastard Programmer from Hell :suss:
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
Each item in inventory should have a unique identifier, but don't count on each having a unique bar code. Manufacturers frequently change bar codes, and you'll often need to have several equally valid barcodes associated with a single item. Another thing to keep in mind is that sometimes a clerk will scan a barcode associated with a case lot, rather than the single item code. I had this happen when I was managing inventory at Ace Hardware. Fluorescent tubes came in a case of 30, and the case had a barcode different from the one printed on each individual tube. Somebody entered the case code into the system as an alternate to the one used for individual tubes, and some of our clerks sold cases at the single tube price. An honest customer pointed out the error, not an observant clerk (of which we apparently had none), but only after we came up short about 1500 tubes. :doh: Another thing to keep in mind is that a single item can come from multiple vendors and manufacturers; this happens a lot for generic items. For instance, we use a ball clevis rated at 30,000 pounds for stringing power lines. There are at least 6 manufacturers of this item, and except for cosmetic differences, they are all the same. We stock one item number, but each will have a different part number (for ordering purposes), and a different barcode. This is actually a very complex kind of program to create, and there are a number of other caveats to beware of, especially if you plan to incorporate features like automatic ordering and receiving. Different vendors stock items with different order multiples, and the conversions between unit counts and order amounts can drive you crazy.
Will Rogers never met me.
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
Thanks everybody for the reply, lots of good info there, thanks
-
Hi there, Anyone knows the best way to use barcode in an inventory and POS system? I mean should I simply just read the barcode and use the Part number as an ID (Primary key) for the item, or is there a better way? Thanks.
As a general rule, I avoid using business data as primary keys. The reason is, some time or the other they're susceptible to change. I understand that modern databases allow cascading updates, but I still feel that it is not an elegant idea and is not worth the pain and effort. I prefer using system-generated keys like GUIDs, IDENTITY columns (and SEQUENCE in Oracle). They're guaranteed to be unique and does not need to change at any point in time. Don't forget to create an index on your barcode number, though.
-
As a general rule, I avoid using business data as primary keys. The reason is, some time or the other they're susceptible to change. I understand that modern databases allow cascading updates, but I still feel that it is not an elegant idea and is not worth the pain and effort. I prefer using system-generated keys like GUIDs, IDENTITY columns (and SEQUENCE in Oracle). They're guaranteed to be unique and does not need to change at any point in time. Don't forget to create an index on your barcode number, though.
Shameel wrote:
As a general rule, I avoid using business data as primary keys
Ahhhh hah hah hah hah slap eh oh sorry... A general rule - it is a cast iron, I will shoot you rule here, and I don't even have a gun. Absolutely NO flexibility in this rule is allowed - ever. The number of times I have gone, oh just this one, and had it come back and bite me is embarrassing.
Never underestimate the power of human stupidity RAH
-
Each item in inventory should have a unique identifier, but don't count on each having a unique bar code. Manufacturers frequently change bar codes, and you'll often need to have several equally valid barcodes associated with a single item. Another thing to keep in mind is that sometimes a clerk will scan a barcode associated with a case lot, rather than the single item code. I had this happen when I was managing inventory at Ace Hardware. Fluorescent tubes came in a case of 30, and the case had a barcode different from the one printed on each individual tube. Somebody entered the case code into the system as an alternate to the one used for individual tubes, and some of our clerks sold cases at the single tube price. An honest customer pointed out the error, not an observant clerk (of which we apparently had none), but only after we came up short about 1500 tubes. :doh: Another thing to keep in mind is that a single item can come from multiple vendors and manufacturers; this happens a lot for generic items. For instance, we use a ball clevis rated at 30,000 pounds for stringing power lines. There are at least 6 manufacturers of this item, and except for cosmetic differences, they are all the same. We stock one item number, but each will have a different part number (for ordering purposes), and a different barcode. This is actually a very complex kind of program to create, and there are a number of other caveats to beware of, especially if you plan to incorporate features like automatic ordering and receiving. Different vendors stock items with different order multiples, and the conversions between unit counts and order amounts can drive you crazy.
Will Rogers never met me.
Very good point, and you are correct. I've programmed POS systems for years. We used what we called an "alias" table for this. Ideally, you will store a system generated unique ID for your product in an items or products table. Then you will supply both a module and an import function to maintain your alias table for storing keyed values associated with your products. ie: CREATE TABLE ALIASES( ID INTEGER IDENTITY(1,1) NOT NULL, ALIAS NVARCHAR(50) NOT NULL, XREF_ID INTEGER NOT NULL, ALIAS_TYPE INTEGER NOT NULL, DESCRIPTION NVARCHAR(100)) Note I used "XREF_ID" rather than a PRODUCT_ID. Keep in mind this table can be used for more than products. For instance, you may want to store aliases for customers, or vendors, or any other things people will look up stuff with. This is where the ALIAS_TYPE comes into play. These types can be maintained by a maintenance module associated with an ALIAS_TYPES table. This allows for dynamic growth of the system and modularization of your cross references. I used an IDENTITY field for simplicity. But as Roger noted, it is probably better to use a GUID or some other form of unique value (i.e. STORE_NO + '-' + IDENTITY). Getting back to the "Barcode" topic... you can store your Barcodes / UPC Codes / Model IDs / whatever in this ALIASES table and give them an appropriate type for searching on.