Database Design Advice
abigail at abigail.be
Fri Nov 8 18:07:07 GMT 2013
On Fri, Nov 08, 2013 at 05:11:14PM +0000, Peter Sergeant wrote:
> On Fri, Nov 8, 2013 at 3:02 PM, Abigail <abigail at abigail.be> wrote:
> > I opted for two columns, "additional" and "percentage", the first
> > defaulting to 0, the other to 1. Prices can then be easily calculated
> > as original_price * percentage + additional. No conditionals needed.
> > And the calculation is currency agnostic. For discounts, the percentage
> > is less than 1, or the additional is negative.
> > This solution proved to be really great -- two years after my
> > implementation
> > I got the request "Can we have discounts from discounts? We'd like to be
> > able to give both a percentage and a set discount" . I told them,
> > "If you just fill in both discounts, it will do what you want".
> Unless what you want is the percentage discount applied to the price after
> the flat discount, which doesn't seem unreasonable.
That's the same as first applying the percentage, followed by the fixed
discount, with the percentage applied to the discount:
P1 = (P0 - X) * (1 - Y/100) = (1 - Y/100) * P0 - X * (1 - Y/100)
Or, to give a concrete example, first giving a 10 Euro discount, followed
by a 5% discount, is the same as first giving a 5% discount, followed by
a EUR 9.50 discount. So, you would store a percentage of 0.95 and an additional
of -9.50. That is, all you need to do is apply the percentage discount
on the fixed discount.
You may want to store a bit somewhere how the information is displayed to
the user, and do the mapping in the application layer, but you don't need
a different database structure, or a different calculation.
As I said, I've been there. ;-) (And no, I don't store that bit, or present
the user with different views. My users know they can get out their
calculators and just subtract the percentage from the fixed discount).
More information about the london.pm