Need advice in designing tables in SQL-Server

Question

Hello all!

I have a quote that contains items (store in table QuoteItem):
QuoteItemId, QuoteId, ItemId, Quantity etc.

Now, I need to be able to create a group of chosen items in the quote and apply a discount on it. Well that's simple, I create two more tables:
Group: GroupId, DiscountPercentage
GroupQuoteItem: GroupId, QuoteItemId

Let's say I have 30 items in a quote. I made a group that contains items 1-20 from the quote and I applied a discount on it. Now I need to have another group that contains items 10-30, the problem is about those inner 10 items, I need to control whether the discount should apply on the items after the other discount or it should be on the items' base price.
For instance, I am gonna talk about item no. 15 in the quote: QuoteItem.Cost = 100 I applied 1st discount of 10% = 90.
Now I want to apply the second discount, I need to be able to control if the discount should be on the 100 or should be on the 90.
Same is when I have multiple discount groups and when I wanna apply a complex architecture of discounts.

Any assistance will be really appreciated.

Answer 1

It really depends on your own business rules. Do you want to apply the discounts on the price after discount or on the original price. When you ask questions like this it helps with SAMPLE Data then show us expected results.

Answer 2

This may be one of those rare times in normalization when you want to store data that you could calculate otherwise. So, in QuoteItem, you could have a Cost field and a DiscountedCost field. If they're the same, then you know no discount has been applied, if they are not, then a discount has been applied. By having this field, you would also be able to do comparisons on what the discount is already and whether you want to add the additional discount. In fact, you could also store that number in an ExistingDiscount field.

Answer 3

I would look into adding a column to the GroupQuoteItem table, GroupQuoteItem.Priority. This column would be used in the query that determines the final price. If you have N discounts with the same, highest priority, they will be stacked atop each other (the order doesn't matter, thanks to associativity of multiplication).

If all of these high-priority discounts are later removed, lower-priority discounts can take their place. This should help you in setting up pretty complex discount structures.

I hope that at least gives you somewhere to start from.

Answer 4

Why not store a column in the Group table that specifies whether or not the discount can be accumulated with other discounts versus if it must be applied to the base price only? You could name the field something like "ApplyToBasePriceOnly."

Other than that, I agree with JonH that a lot of this logic should be placed in business rules. I think your general database structure looks pretty good.