Product sales & Price discount

Question asked by nickos on Feb 6, 2018
Latest reply on Mar 3, 2018 by beverly

Dear all,

I turn to you as it's a long time I try to create a database managing product sales with discount according to quantity sold. I don't know how to start to create it...

I explain.

I have a list of products (red apple, green apple, pineapple, grappefruit, different type of potatoes, different type of beans, tomatoes...), and I need to regularly manage discount % on price depending on quantity of purchased products.

Then, discount can be settled:

- on one product, ie: if purchase quantity is above 5, discount price = 5%, if purchave is above 10, discount price = 10%, etc...

- on group of products: ie: for a selection of products, if sum of quantity is above 3, discount price = 3%, if sum of quantity is above 6, discount price = 7%, is sum of quantity is above 10, discount price = 9%, etc...

Additionnally, I need to manage 2 types of discounts, if a group of products reach a certain amount of quantity or not...

Example : Let's take the fruits and vegetables example...

For vegetable and fruits, regularly I set up discounts % for range of purchased quantity.

For all fruits, total Quantity purchased > 5 then discount 2% of normal price, total Quantity > 10 then discount 4%

Additionnally to that, if all fruits amount is > 50\$ (on the normal price without rebate) , the discount is set to 10% of normal price...

For vegetables it's different:

• For potatoes+ asparagus total Quantity > 5 then discount 3%, total Quantity > 10 then discount 6%
• For all type of beans, Quantity > 5 then discount 1%, Quantity > 7 then discount 2%, Quantity > 10 then discount 3%
• for each green tomato, Quantity > 2 then discount 2,5%, Quantity > 4 then discount 5%
• for red tomato, Quantity > 1 then discount 1,5%, Quantity > 3 then discount 3%

If the total fruit amount is >50\$, then  discount for these above vegetable is different with different setup.

Knowing all that, I don't know how to start to setup my database...

I have the product table

Where can I manage the discount panel and these parameters? I need to create a discount table?

Well, if you have any idea on where I can start, it would help me start... at least .