what is an optimized way of product discount in DB?

#database #sql

I have product price based on 3 different ways
1) Fixed (single)

2) Vary by the quantity range (
1-100 = $60
101-200 =$55
201-300=$50
301-400=$45
401-500=$40

3) Vary by size
M =$50
L= $60
XL=$70

but one type at a time, so should I create a discount price column with a discount percentage, or just calculate at run time.

Top comments (3)

Ben • Oct 8 '20 • Edited

The way my team works is to have separation.

Tables.

Product
Attributes (S,M,L etc all with prices for the individual attribute) FK product uid
AttributeDiscounts (type (enum Fixed, percentage etc), value int) FK to attribute uid
ProductDiscounts (type (enum Fixed, percentage etc), value int) FK to product uid

Attribute Discounts override Product Discounts.

If you apply this principal to your data, you would need to extend AttributeDiscounts/ProductDiscounts to accept a min & max items for your bulk discounts.