I am really hoping someone can help me out here. I'll try and provide as much detail as I can.
I have taken it upon myself to create a 'simple' application for the company I work for which will allow us to assign barcodes to products.
As it stands we have an excel spreadsheet which contains our barcode numbers and it is a colleagues responsibility to go into that spreadsheet and enter the new product SKUs and descriptions against an available barcode and to also use another program to generate the check digit for each barcode and also add that to the record. ... a long winded and manual process which I would like to simplify for them.
Further to this, the process will be changing over the summer as the company moves towards an automated warehouse system that requires us to have not only barcodes for the individual products (an EAN13 code), but also an option to store a different barcode (GTIN14) for the product being sold in sales of 3 (for example - we refer to this as a pack) and also for a master carton (a particular number of packs) if these are needed. Not all products that require a EAN13 code will require a GTIN14 code.
So I foresee an application where my colleague can create a new SKU and the app will assign the SKU a valid EAN13 barcode and also a GTIN14 for the pack and also for the master carton (should they be required).
To elaborate on these barcodes, we have a set range of barcodes we can use. lets say, they are the numbers 0 - 10,000. Of course these are actually 12 digits long and with the check digit it becomes a valid 13 digit EAN13 code. The GTIN14 codes are actually just the EAN13 code with a 0 prefixed to it. However in order for the pack and master barcodes to scan correctly they cannot just be the products EAN13 and a 0 at the front. So we have split the range of available barcodes as such that the first 30% are for EAN13 codes the next 30% are for the GTIN14 for packs and the next 30% are the GTINs for the masters.
Over the past 8 months I have been gaining experience with application GUI design in Visual Studio 2010 so I am keen to put my new found knowledge into practise, however in all my learning about creation of applications I have always been provided with a database to work with and I haven't actually tried to design a database since I was at school (~15 years ago!).
And that is where I am coming unstuck.
I think I have identified the need for a Product table which will hold the SKU and the Description. Through my recent research I have also seen that I cannot use SKU as a Primary Key for my table as the SKU could change and also a post on this very forum where user @r937 pointed out (to another user) that I wouldn't actually have 1 product with 3 barcodes, but rather 3 products with a barcode each. So from that information I think I will need to create a ProductID field which can act as my PK in my Product table.
And is pretty much as far as I can get. I am not sure how to go about storing the barcode information and how to relate that to the Product table. I feel as though it cannot be one table holding a BarcodeID (PK), a BarcodeBase, a calculated CheckDigit field and then further calculated fields for the optional GTIN14 barcodes as the GTIN14 codes will be calculated from a different BarcodeBase to that which will be used to calculate the EAN13 code.
So perhaps 3 tables (one for EAN13, GTIN14 for packs and one for GTIN14 for master cartons)? Would the relationship between the Product table each of the barcode tables be a one to one based on the PK for each table?
Perhaps it should be Product table and then a Barcode table which has 3 child tables one for each type of barcode that may or may not be required?
Furthermore, how do I make the BarcodeBase a unique number that falls within my desired range. I have taken advise that I could use a CHECK CONSTRAINT however I shouldn't do that on an unique field (using AutoIncrement as that is INDENTITY (?) ) - perhaps these questions are for another thread?
In any case, as you can see I have gotten myself into a complete muddle and any help would be greatly appreciated!