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 .

Thanks for your help...

Niko

A few questions, if answered, will help others in helping you:

Do you use the typical invoicing data model where you have one record for the invoice and a related table of line items (often called Invoice Data) for listing the different products sold on that invoice? (Typically, a portal to the Line Items table is placed on the invoices layout for listing the items sold.)

Are these discounts all ones that need to be defined in advance so that they are applied automatically or is there additional user input needed such as choosing whether or not to apply the discount that would otherwise apply?

Now check this to confirm whether or not I understand you correctly:

This leads up to one more question. Is it possible that a given product might be a member of more than one discount group?

If so, what should happen if a product, by being a member of two discount groups qualifies for two discounts? (and causes two different groups of products on the invoice to qualify for different discounts....) Hopefully, you don't have any products that are members of more than one group as this complicates your process quite a bit.