Database Design Advice

[Abigail]

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" [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