Return to Answer

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

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)

-- 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.