On Fri, Nov 08, 2013 at 05:11:14PM +0000, Peter Sergeant wrote: > On Fri, Nov 8, 2013 at 3:02 PM, Abigail <abig...@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" [1]. 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). Abigail