DEV Community

Shareque
Shareque

Posted on

what is an optimized way of product discount in DB?

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)

Collapse
 
bjkippax profile image
Ben • Edited

The way my team works is to have separation.

Tables.

  1. Product
  2. Attributes (S,M,L etc all with prices for the individual attribute) FK product uid
  3. AttributeDiscounts (type (enum Fixed, percentage etc), value int) FK to attribute uid
  4. 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.

Collapse
 
iamshareque profile image
Shareque

thanks, Ben I will try this

Collapse
 
bjkippax profile image
Ben

No worries. Any issues, reply here and I can provide some further guidance. I have notifications turned on.

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay