Best way to store product size/quanties in a database?

Question

Hello all,

I'm building a custom shop and one thing that none of us here can agree on is how we should store the size/quantity fields for a product. I easily can't find information on this and am wondering how people have tackled this before?

Currently we are thinking of either storing the complete size/quantity information in something like JSON for each product in one field or else creating a variably sized key/value list which is then tied to a product.

What way do you choose to store this info?

Answer 1

The traditional way is to have a look-up table of units, such as "oz", combined with a quantity that is not an integer.

Answer 2

I'm assuming that you can have multiple non-calculable size/quantities for a product.

e.g. Pack, 12 Box, 22

If you're doing this in a database, then current thinking says you need to normalise and create a new table containing a link to the product (product_ID), a size and a quantity. This is what I'd do, but I program in a language that doesn't have JSON or key/value pairs.

However - I think the solution you choose depends greatly on what you'll be doing with the size/quantity values in your application. Are these simply displayed, or are they possibly going to be used down the line for other purposes? Also - is there a default size/quantity?

Answer 3

There are several ways to tackle this, each of which increases in complexity:

Don't store unit information:

The item record can store it in the description.

Item Table:

ID | Description       
---+---------------------------------
09 | Red Sox Tee Shirt (Case of 12)
10 | Red Sox Tee Shirt (each)
11 | 500lb Nylon Rope  (per foot)

Then on the order table, you simply need to store an Item ID + Quantity where quantity is a decimal value.

Store the unit as part of the item

Description       | Unit | UnitPrice    
------------------+------+----------
Red Sox Tee Shirt | Case |     45.00
500lb Nylon Rope  | Foot |      0.25

Then on the order table, you simply need to store an Item ID + Quantity where quantity is a decimal value.

Store convertible units as related child table of Item

Item Table:

ID | Description       
---+----------------
10 | Red Sox Tee Shirt 
11 | 500lb Nylon Rope

Item_Unit table

ID | Unit   | UnitPrice
---+--------+----------
10 | Each   |      6.00
10 | Case12 |     60.00
11 | Foot   |      0.25
11 | Spool  |    250.00

In this case, the order record would need to have Item ID + Unit + Quantity

Most flexible and complicated -- convertible units

This would be an extension of the last form, but one where you have a "Unit" table that stores units, their unit type (length, volume, etc...) and their relation to a common unit. That way you could actually convert from one unit to another (1000 feet == 1 spool) for example.

But it is unlikely that you are in need of that level of complexity.