Postgresql – the better way to handle a different types of measurement in grocery store items database

database-designpostgresqlpostgresql-9.3schema

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?

Best Answer

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
Related Question