5 added 27 characters in body
source | link

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 percentagediscount (percentage-modifier, with PostgreSQL that looks something like this). 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

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 percentage-modifier, with PostgreSQL that looks something like this.

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
);

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

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
4 added 13 characters in body
source | link

Then you just do something like thisThat's all that's needed for the schema. Here are some data points about the transaction,

--SELECT Wepid,
 select thepurchased,
 total pricepurchased*unit_price AS subtotal, 
 minus theqty sumAS ofsavings_bracket,
 all therange_count(int4range(0,purchased)*qty) discountsAS savings_num,
SELECT purchased*unit_price -(percent_modifier * 100)::int AS savings_percent,
  coalesce(
  sum(  range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier),
    0
  )::numeric(6,2) AS pricesubtotal_savings
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);
GROUP BY purchased, unit_price;
pid | purchased | subtotal | pricesavings_bracket | savings_num | savings_percent | subtotal_savings 
-----+-----------+----------+-----------------+-------------+-----------------+------------------
 620  1 |       250 |   585.10000052303100 | [150,200)       |          50 |              10 |            11.70
(   1 row|       250 |   585.00 | [200,250)       |          50 |              20 |            23.40
(2 rows)

IfThen you needjust do is group by pid and apply the subtotal_savings to know how that works, look at this.the subtotal to get the total

SELECT pid,
-- We purchased*unit_priceselect ASthe total price,
  minus qtythe sum of all the discounts
SELECT p.pid,
  coalesce(
purchased*unit_price AS subtotal,
  sum(range_count(int4range(0,purchased)*qty) * -1 * 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(68,2) AS discounttotal
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);

GROUP BY p.pid, |unit_price, totalpurchased;
 pid |    qty subtotal | savings | discounttotal  
-----+--------+--+---------+----------
   1 | 585.00 | [150,200) |   -11.70
   1 | 585.00 | [200,250) | 35.10 | -23549.4090
(21 rowsrow)

That tells you your total for the PID, and the discounts applied for buying in that range.

Then you just do something like this,

-- We select the total price, minus the sum of all the discounts
SELECT purchased*unit_price - coalesce(
  sum(range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier),
  0
) AS price
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid)
GROUP BY purchased, unit_price;
      price       
------------------
 620.100000523031
(1 row)

If you need to know how that works, look at this.

SELECT pid,
  purchased*unit_price AS total,
   qty,
  coalesce(
    range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier,
                0
  )::numeric(6,2) AS discount
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);

 pid | total  |    qty    | discount 
-----+--------+-----------+----------
   1 | 585.00 | [150,200) |   -11.70
   1 | 585.00 | [200,250) |   -23.40
(2 rows)

That tells you your total for the PID, and the discounts applied for buying in that range.

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)
3 added 13 characters in body
source | link
-- We select the total price, minus the sum of all the discounts
SELECT t*unit_pricepurchased*unit_price - coalesce(
  sum(range_count(int4range(0,tpurchased)*qty) * -1 * unit_price * percent_modifier),
  0
) AS price
FROM ( VALUES (250,1) ) AS tp(tpurchased, pid)
CROSSINNER JOIN product USING (pid)
JOIN product_discount USING (pid)
GROUP BY t.tpurchased, unit_price;
      price       
------------------
 620.100000523031
(1 row)

If you need to know how that works, look at this.

SELECT pid,
  purchased*unit_price AS total,
  qty,
  coalesce(
    range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier,
                0
  )::numeric(6,2) AS discount
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);

 pid | total  |    qty    | discount 
-----+--------+-----------+----------
   1 | 585.00 | [150,200) |   -11.70
   1 | 585.00 | [200,250) |   -23.40
(2 rows)

That tells you your total for the PID, and the discounts applied for buying in that range.

-- We select the total price, minus the sum of all the discounts
SELECT t*unit_price - coalesce(
  sum(range_count(int4range(0,t)*qty) * -1 * unit_price * percent_modifier),
  0
) AS price
FROM ( VALUES (250) ) AS t(t)
CROSS JOIN product
JOIN product_discount USING (pid)
GROUP BY t.t, unit_price;
-- We select the total price, minus the sum of all the discounts
SELECT purchased*unit_price - coalesce(
  sum(range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier),
  0
) AS price
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid)
GROUP BY purchased, unit_price;
      price       
------------------
 620.100000523031
(1 row)

If you need to know how that works, look at this.

SELECT pid,
  purchased*unit_price AS total,
  qty,
  coalesce(
    range_count(int4range(0,purchased)*qty) * -1 * unit_price * percent_modifier,
                0
  )::numeric(6,2) AS discount
FROM ( VALUES (250,1) ) AS p(purchased, pid)
INNER JOIN product USING (pid)
JOIN product_discount USING (pid);

 pid | total  |    qty    | discount 
-----+--------+-----------+----------
   1 | 585.00 | [150,200) |   -11.70
   1 | 585.00 | [200,250) |   -23.40
(2 rows)

That tells you your total for the PID, and the discounts applied for buying in that range.

2 added 13 characters in body
source | link
1
source | link