Optimal way to store units of measure for Stock items

Question

Hi all,

Assuming a schema structure as such.

-----------------------------------------
Stock (ID, Description, blah blah)
-----------------------------------------
StockBarcode (ID, StockID, Barcode, Price, blah blah)
-----------------------------------------

What is the optimal way of storing units of measure for your stock items? Given that the StockBarcode Price may be for 1 item, for 10 Items, for 10 grams or for 10 pounds?

Stock to StockBarcode is a one to many relationship. (Although Im sure you didnt need me telling you that)

Cheers :)

Answer 1

I'd be adding Qty and UOMID columns to the StockBarcode table and then a new table like

StockUOM (ID, Description)

Answer 2

If i understand your stock table correctly, it consists of the products that you are stocking to sell.

One option to consider is that instead of stock data maybe you should consider keeping the data based on what in stock keeping parlance, is referred to as SKU (stock keeping unit) Information.

Each SKU itself can be made of more than 1 item but as it cannot be sold that way, you dont have to concern yourself with those details in most scenarios. Details like price etc are all properties that are then associated with the SKU.

Eg: If a product say beer can be sold individually / 6 pack / 12 pack then it has 3 SKU's associated with it.

Then you have relationships :

Products --> SKU's which is 1:many

SKU --> StockBarCode which is 1:1 (assuming you have the same bar code for all the units of the same SKU - if not then it can be 1: Many as well)

Answer 3

Is there a unique StockBarcode for every UOM? For example, is there a barcode for grams, a barcode for pounds and a barcode for individual items? If so, Andrew's solution will work.

If not, you will need to create another table that contains the StockID, Qty and UOMID's.

StockUOM (ID, Description)

StockCount (ID, StockID, UOMID, Qty)

When you scan the barcode, you will need to enter in what UOM you are scanning for. Then the software can update the StockCount table based on item scanned. This could be a good fallback in case your items don't have more than one barcode, and you are stocking more than one UOM (common).