What is the better way to handle a different types of measurement in grocery store items database

Question

While designing a DB for grocery shop, I came across a requirement of "same product available in different measurements" - e.g., Lets say, Green Peas available in 250gms, 500gms, 1Kg and so.

My question is : Is it good practice to have duplicate entry of product with different measurement in products table or keeping the measurements in different table with product id?

Answer 1

Using postgresql-unit

I'm big on types. I would use an extension for this. If you're not in the US and working with Imperial Units, take a look at Christoph Berg's brainchild postgresql-unit. First build the extension

sudo apt-get install bison flex build-essentials libpq-dev
git clone "https://github.com/ChristophBerg/postgresql-unit"
cd postgresql-unit
make
sudo make install

Then install the extension on your DB and configure your table.

CREATE EXTENSION unit;

CREATE TABLE products (
  id     serial        PRIMARY KEY,
  name   text          NOT NULL,
  unit   unit          NOT NULL,
  price  numeric(7,2)
);

INSERT INTO products ( name, unit, price )
VALUES ( 'Green Peas', '250 g', '2.99' );

# SELECT name, unit, price FROM products;
    name    | unit  | price 
------------+-------+-------
 Green Peas | 250 g |  2.99

# SELECT name, unit @ 'kg' AS unit, price FROM products;
    name    |  unit   | price 
------------+---------+-------
 Green Peas | 0.25 kg |  2.99

Likewise you can INSERT in other units if need be. They'll get stored in those units, but you can select in any unit you want.

INSERT INTO products ( name, unit, price )
VALUES ( 'KY Jelly', '1 kg', '8.00' );

# TABLE products;
 id |    name    | unit  | price 
----+------------+-------+-------
  2 | Green Peas | 250 g |  2.99
  3 | KY Jelly   | 1 kg  |  8.00

# SELECT id, name, unit @ 'g' AS unit, price FROM products;
 id |    name    | unit   | price 
----+------------+--------+-------
  2 | Green Peas | 250 g  |  2.99
  3 | KY Jelly   | 1000 g |  8.00

Answer 2

You will probably find it easier to store each product with a different size, once per line on the product table. This seems like it breaks normalisation rules ( I suppose it does a little bit), but over time as you add/remove items and manufacturers create new flavours of products but in slightly different size combinations, you will end up splitting up your products in the products table too much for maintenance.

A good example is chocolate bars.

Nestle come out with a new chocolate bar "Fraggleiscious". A year later they release a "Fraggleicious Mint". 2 months later they reduce the mint size bar by from 50g to 45g and change the price point while leaving the original bar the same size. 4 months later they produce a "Fraggleicious Mini" in both flavours, both at 25g. Remember that a different barcode may or may not be issued with each change.

Your system must cater for all of this and be able to recognise all items at any time as it may take you 2 years to sell out of all of them. You will probably find that having a single Product table with Name/Flavour/Size combination to be best so you would have:

Fraggleicious / Original / 50g
Fraggleicious / Mint / 50g
Fraggleicious / Mint / 45g
Fraggleicious Mini / Original / 25g
Fraggleicious Mini / Mint / 25g

Answer 3

The safest way is to define product_id's with the finest reasonable granularity, to the point that if you decide to sell half the stock at a discounted price, you should define a new product_id for the items in promotion, all things being equal but the price. In this way you will manage to balance sales and returns without too many corrections. So I basically agree with blobbles's answer. You will have many ways to group your products together, e.g. same name, same size, same producer, same provider, and so on, and you will do that in separate tables associating the product_id with those features.

Answer 4

I'm making an assumption here that the products are (mostly) pre-packaged.

The decision has already been made for you. Each product has a UPC code, and it will be different on each flavour / package size etc. That's your primary product key. So yes, the 250g peas and the 500g peas will be different entries for two reasons:

1. the only common item between them is the producer and the name
2. the producer already decided they are different products by virtue of assigning a different UPC number to each size.

Use a local UPC range for non-tagged items like fresh meat and bulk vegetables.

Grocery inventory management has been around a very long time, no need to re-invent the wheel here.