0

Let's say I have something that represents an item, let's say Beer. I store it in a table "Items". I also have a table with units of measurements. Like box, as beers can be sold in boxes.

enter image description here

Now the problem is, the box can have different amount of beers, there can be a box of lets say 20 beers and there can be a box of 10 beers. So there are a couple of possible conversion factors depending on the item.

Also the item can be sold in different unit, like a box, pack and each box and pack can have different amounts of items inside.

How should I design my database to handle this problem?

  • Since the count per box can vary by the type of beer I would keep the box quantity stored with the beer details. – Dave Apr 12 '18 at 11:49
1

Your problem is not a unit of measurement problem. It's a stock keeping unit (SKU) problem. You can't convert, nor for any practical purposes would you ever want to convert from 8 packs of cans of beer A to 12 packs of bottles of beer B.

Your product ("Beer A") is a generic product header. You don't sell "Beer A". What you actually sell are the boxes of cans (or bottles) - of a particular size - of Beer A. This box is what gets a record in your product/item table. You may want to have a product header table that just lists "Beer A" without any actual quantity related to it. That's up to you and depends on your system requirements. What you really need to have though is a SKU table where the actual salable things are recorded.

Now if you want to be able to get some kind of apples to apples comparison between SKUs of similar products, you can track an actual, objective unit of measure for each SKU. For example, if you're dealing with beer, then a liquid measure is appropriate. Say you have 6 x 355ml cans of Beer A in your six pack SKU. Then that SKU has 2,130ml of beer in it. You could store that along with the other useful information that it's a six pack of 355ml cans.

If you want to have this apples to apples comparison, then you want to have a unit of measure conversion table that has columns such as:

...
  measure_type char(1) -- `D`ry or `L`iquid (or others as applicable)
, from_unit_of_measure varchar(10)
, to_unit_of_measure   varchar(10)
, conversion_factor    decimal(16,8)
...

If you have something based on these columns then you have to have all the combinations of from / to units of measure in both directions in order to look up a conversion factor. You could also, alternatively, only have from non-default units of measure to the standard unit of measure for each measure_type. This would mean less data in the table, but you'd have to have conditional logic to go from and / or to non-standard units of measure. In the worst case scenario, you might have to convert from units in A (non-standard) to B (standard) and again from B (standard) to C (non-standard). Which way you go depends on your relative tolerance for more data to maintain or more code complexity.

  • Do you mean to store box of Beer A as a separate product and not a unit? – Konrad Apr 13 '18 at 7:25
  • Like "Box of 10 beers", "Box of 20 beers" – Konrad Apr 13 '18 at 7:26
  • There also can be different purchase unit of measure and sales unit of measure. So the beer can be bought in let's say a box and sold in pieces. – Konrad Apr 13 '18 at 7:28
  • 2
    @LoneWolfMcQuade yes, that's right. SKUs are specific units of sale, so "Box of 10 beers" - probably of a specific beer is a single SKU, unless you're selling pick and mix type boxes, in which case "Box of 10 assorted beers" is a reasonable SKU. It's also true that you want to have SKUs for receiving which might be different from SKUs for selling. In that case you can have a bill of materials table that gives the composition of selling SKUs within a given receiving SKU. – Joel Brown Apr 13 '18 at 11:06
  • what if customer will open the box and just take 1 product from there(because this can happen, e.g. in the supermarket), if you store box as a separate product then how will you sell it then? – Konrad Apr 13 '18 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.