CODEWITHSUNDEEP
databasedatabase-designoopschema
Maxim Gershkovich
Maxim Gershkovich

Reputation: 47189

Optimal way to store units of measure for Stock items

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 :)

Upvotes: 0

Views: 2840

Answers (4)

Eric Wood
Eric Wood

Reputation: 41

You might think about putting an additional UOM column on ALL tables where Qty fields are and additional Currency column on all money columns.

ALL entry screens should ask Qty and UOM.

Item table - add Inventory/Stocking UOM, Purchasing/Receive UOM, Pricing UOM, Shipping/Send UOM, Production UOM, Component UOM columns

new UOM table - ID, Abbrev., Description, RegionID, UOMTypeID

new UOMRegion table - ID, Code, Description (example data - 1, UK, United Kingdom; 2, US, United States; 3, INT, International)

new UOMType table - ID, Code, Description, DefaultUOMID (example data - 1, V, Volume, 15; 2, A, Area, 45; 3, W, Weight, 32; etc.)

new UOMConversionFactor table - ID, FromUOMID, ToUOMID, ConversionFactor (example data - 1, 1, 1, 1; 2, 1, 3, 0.026021066655565; 3, 3, 1, 38.430399999999000)

(notes - Conversion from UOM to same UOM is 1. May put in table or not. Each record I usually have an implied column FromQty which is always 1. Make sure ConversionFactor allows for HUGE numbers so final numbers turn out more accurate when large Qty are involved)

Thoughts - 1) some UOM are not specific enough such as "barrel" (there is a US dry goods barrel, a US barrel for cranberries, U.S. fluid barrel, U.K. a beer barrel, US beer barrel, oil barrel, etc.), 2) UOM is impacted by region if you are worrying about an international application (ie. a US cup is different from UK cup that is different than the international cup), 3) I can get a receive a cartoon of item X, store it in pallets, and ship it in eaches. 4) "kit" or "build" items can be raw materials in UOM plus various components in different UOM that ultimately results in a final product in a different UOM.

Upvotes: 4

Rots
Rots

Reputation: 5586

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).

Upvotes: 0

Jagmag
Jagmag

Reputation: 10366

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)

Upvotes: 0

Andrew Cooper
Andrew Cooper

Reputation: 32586

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

StockUOM (ID, Description)

Upvotes: 3

Related Questions