4

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?

  • It depends, how will you store it price? – McNets Mar 9 '17 at 19:47
  • Ha good one. right now, price is stored in product table. – Itzdsp Mar 9 '17 at 19:51
  • 1
    Another situation you will find is that there is a separate barcode for the case, box and each. So candy bars come in boxes within a case, each have their own count, but they are only sold in one unit. However, receiving and invoicing might be at the case level, with sales at the each level. – Cade Roux Mar 9 '17 at 22:47
4

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
  • 3
    Good to know abt unit in postgres. but question is whether i should save same product multiple times with different measurement or not. Looks like i need to break the table more granular like @dario said. – Itzdsp Mar 9 '17 at 21:58
  • It doesn't matter if you use the unit type. Ie, there is no reason. Just select in the unit you want. It works just like timestamps. You don't store time in every time zone. You store it in a single timezone (UTC) and then you read it in whatever timezone the client wants it in. – Evan Carroll Mar 9 '17 at 22:00
  • This doesn't answer the question that was asked. It shouldn't be upvoted. – user253751 Mar 10 '17 at 1:54
  • I'm not sure how... You can only have one price/qty for a product. Unless you're selling the same product at two different prices (maybe you are wholesaling too or something). Shy of that scenario though, this data is fully denormalized right here. There is no redundant data. It's a price sheet from which you can derivce any units pricing. – Evan Carroll Mar 10 '17 at 2:01
3

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
  • 1
    This is good if you have just one or two items in that type. 1. What if user wants to select Fraggleicious / Original / 50g , 45g, 30g each one. 2. Also displaying data in front end wont be good. we will be showing many "Fraggleicious / Original" with different size. – Itzdsp Mar 9 '17 at 20:04
  • sorry if am wrong, am new to db design. I completed the basic db design and while preparing data for testing, i encountered this problem and realized this designing schema is such a pain. – Itzdsp Mar 9 '17 at 20:09
  • 2
    Why is that a problem? Think about it from the other side - if the user cannot easily see the differences between all different items, that may also be an issue. So if the user were to buy 7 different bars and you aggregate all 7 to a single line "Fraggleicious x 7", that is not a good user experience. They cannot easily see how many mint flavours they have bought, for instance, nor how many bars of different sizes. – blobbles Mar 9 '17 at 20:34
  • 2
    Indeed if you look at a supermarket receipt you will probably only see aggregation where you have exactly the same item in terms of size/flavours. – blobbles Mar 9 '17 at 20:36
2

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.

  • Yeah right, am goinf going group it with name -> measurement in dropdown[with rate]. – Itzdsp Mar 9 '17 at 21:56
2

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.

  • You can also tell they're different products by looking at them - one is bigger than the other, one says 250g where the other one has 500g, and so on. – user253751 Mar 10 '17 at 1:57
  • different products? he said explicitly "same product available in different measurements" – Evan Carroll Mar 10 '17 at 2:30

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.