Efficient database design for multiple price based on quantity and attribute

Question

I have a data sample for products with different prices depending on quantity and attributes. The price might be updated occasionally (not too frequently).

PID | Name                | Type  | Colour  | Colour Print | Quantity: 1 to 149 |   150 to 199| 200 to 249 |250 to 499
1   |White ABC Product    |ABC    |White    |1CP           |2.34|2.07   |1.82|1.51
2   |White ABC Product    |ABC    |White    |2CP           |2.6 |2.31   |1.97|1.62
3   |White ABC Product    |ABC    |White    |3CP          |2.86|2.55|2.14|1.77
7   |Red ABC Product      |ABC    |Red      |1CP           |2.39|2.12|1.87|1.56
8   |Red ABC Product      |ABC    |Red      |2CP           |2.65|2.36|2.02|1.67
9   |Red ABC Product      |ABC    |Red      |3CP           |2.91|2.6|2.19|1.82
12  |White XYZ Product    |XYZ    |White    |1CP           |2.69|2.38|2.09|1.74
13  |White XYZ Product    |XYZ    |White    |2CP           |2.69|2.38|2.09|1.74
14  |White XYZ Product    |XYZ    |White    |3CP           |3.29|2.93|2.46|2.04
18  |Emerald  XYZ Product |XYZ    |Emerald  |1CP           |2.74|2.43|2.14  |1.79   
19  |Emerald  XYZ Product |XYZ    |Emerald  |2CP           |3.04|2.71|2.32  |1.91   
20  |Emerald  XYZ Product |XYZ    |Emerald  |3CP           |3.34|2.98|2.51  |2.09

So basically, I can design the database as the same structure as the above data sample and dump all data into the table (a lot of repetition) or I can do something like this:

Product Table:
---------
--Product id
--Product name {ABC, XYZ, PQR...so on}


Colour Table:
-------
--Colour id
--Colour value {white, red, emerald, and so on}

Colour Print Table 
--------
--Colour print id
--Colour print value {1CP, 2CP, 3CP}

Quantity Table
--------
--Quantity id
--Quantity value {1-149, 150-199, 200-249,250-499}

Price Table
-------
--Price id
--Product id
--Colour id
--Colour Print id
--Quantity id
--Price

So the price table will look something like this:

Price table                 
price id |  product id |colour id | quantity id | colourprint id |  price
1        | 1           |1         | 1           |1               |2.34
2        | 1           |1         | 2           |1               |2.07
3        | 1           |1         | 3           |1               |1.82
4        | 1           |1         | 4           |1               |1.51
5        | 1           |1         | 1           |2               |2.6
6        | 1           |1         | 2           |2               |2.31
7        | 1           |1         | 3           |2               |1.97
8        | 1           |1         | 4           |2               |1.62
9        | 1           |1         | 1           |3               |2.86

Price is displayed to the user when they choose the options based on type of product, colour, colour print and quantity.

New products might also be added in the future. I'm looking to design the database in the most efficient way.

I find the first option easier to implement but not sure if it's the most efficient or if it's even correct.

Your input would be appreciated. Thank you!

Answer 1

First of all you must define your entities and these form your tables. From what you provide above I can see two tables based on two entities: Product and price.

Then you will add your attributes to each entity such as colour and colour print.

Then you should decide on the nature of your relationships - product has a 1:M relationship with price.

To avoid anomalies in your database you will have to normalise your tables - see https://beginnersbook.com/2015/05/normalization-in-dbms/

Hope this helps :-)

Answer 2

Using PostgreSQL

Looking at the sources of redundancy there, I would modify that a bit. Rather than storing the price between a range, I would store the discount (percentage-modifier). Here we install an additional EXCLUSION CONSTRAINT to ensure the discount ranges are not overlapping.

CREATE FUNCTION range_count(r int4range)
RETURNS int AS $$
  SELECT upper(r)-lower(r)
$$ LANGUAGE SQL
IMMUTABLE;

CREATE TABLE product (
  pid        int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name       text,
  type       text,
  color      text,
  unit_price numeric(6,2),
  UNIQUE ( name, type, color )
);

CREATE TABLE product_discount (
  pid               int       REFERENCES product,
  qty               int4range,
  percent_modifier  real,
  EXCLUDE qty WITH &&
);

INSERT INTO product (name, type, color, unit_price) VALUES
  ('White ABC Product', 'ABC', 'White', 2.34);

INSERT INTO product_discount VALUES
  (1,'[150,199]', 0.10),  -- 10% off all over 150
  (1,'[200,249]', 0.20);  -- 20% off all over 200

That's all that's needed for the schema. Here are some data points about the transaction,

SELECT pid,
  purchased,
  purchased*unit_price AS subtotal,
  qty AS savings_bracket,
  range_count(int4range(0,purchased)*qty) AS savings_num,
  (percent_modifier * 100)::int AS savings_percent,
  coalesce(
    range_count(int4range(0,purchased)*qty) * unit_price * percent_modifier,
    0
  )::numeric(6,2) AS subtotal_savings
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);
 pid | purchased | subtotal | savings_bracket | savings_num | savings_percent | subtotal_savings 
-----+-----------+----------+-----------------+-------------+-----------------+------------------
   1 |       250 |   585.00 | [150,200)       |          50 |              10 |            11.70
   1 |       250 |   585.00 | [200,250)       |          50 |              20 |            23.40
(2 rows)

Then you just do is group by pid and apply the subtotal_savings to the subtotal to get the total

-- We select the total price, minus the sum of all the discounts
SELECT p.pid,
  purchased*unit_price AS subtotal,
  sum(range_count(int4range(0,purchased)*qty) * unit_price * percent_modifier)::numeric(8,2) AS savings,
  purchased*unit_price - coalesce(
    sum(range_count(int4range(0,purchased)*qty) * unit_price * percent_modifier),
    0
  )::numeric(8,2) AS total
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid)
GROUP BY p.pid, unit_price, purchased;
 pid | subtotal | savings | total  
-----+----------+---------+--------
   1 |   585.00 |   35.10 | 549.90
(1 row)