Efficient database design for multiple price based on quantity and attribute

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!

• Do you have a DBMS in mind? Do you need to maintain pricing history (e.g. based on purchase date)? If I buy 250 of product X, do I get all 250 at the 250-499 price, or do I pay one price (1-149) for the first 149, another price (150-199) for the next fifty, another price (200-249) for the next fifty, and a fourth price (250-499) for the last one? Do you support orders over 499 items? Jan 15, 2018 at 3:28
• What do you mean by efficient? Best performance? Good maintenance? Jan 15, 2018 at 6:07
• @mathewb, no there's no need to maintain of pricing history. Yes, all of the products will be at 250-499 price. For orders over 499, the customer has to email his requirements to the website owner directly. Jan 15, 2018 at 12:00
• @Nick.McDermaid, both. Jan 15, 2018 at 12:00
• To expand on @Nick.McDermaid's question - best performance for storage? best performance for response time? Jan 15, 2018 at 15:44

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

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)
``````
• Thank you for your answer The price range is set by the manufacturer. Unfortunately, I cannot modify that. Jan 15, 2018 at 12:02
• @input you'll have to adopt the above for your use case. you didn't tell what is supposed to happen if the mfr excludes a range for instance if 1-50 is null, or if you have 1-50 and 100-150 and such. Your rule system wasn't complete so I left it out. You essentially need to ensure a range covers all positive intergers. Jan 16, 2018 at 0:52