Database Design Advice

[Abigail]

On Fri, Nov 08, 2013 at 11:43:08AM +0000, Smylers wrote:
> I wrote:
> 
> > Hello. I'm designing a database schema, and am interested in any
> > wisdom folk can share over a few aspects of it:
> 
> Thanks for all the advice so far. One more question I forgot to ask in
> the initial mail:
> 
> A discount can either be a percentage or a value in euros. I can think
> of several suboptimal ways of representing this:
> 
> • Separate fields for discount_percentage and discount_amount. This has
>   the disadvantage of needing to ensure that a record doesn't have both
>   fields set.
>   
>   If the business concocts another discount type in future, this will
>   require a field for each discount type.
> 
> • A discount_type field which indicates either 'percent' or 'euro' (or
>   NULL for no discount), then a numeric field which stores either the
>   percentage of the euro amount. This seems really icky, having a field
>   which can mean one of two different things, just that they both happen
>   to be numeric. 
> 
>   This approach always uses 2 fields regardless of the number of
>   discount types. But it forces all of them to be numeric.
> 
> • Have separate euro_discount and percentage_discount tables, then when
>   a discount applies create a record in the appropriate table. This
>   avoids any NULLs in the DB (something I've seen advocated as good
>   database design), but it still requires ensuring that both discount
>   types don't get used at once.
>   
>   It also makes answering the question ‘is there any discount?’ more
>   work, and something which will get worse if the business concocts
>   another discount type in future.
> 
> All suggestions gratefully received.


Been there, done that.


I have had the same businesss requirement in the past, except that it was
to generally derive a price from another price -- which meant not just 
discounts, but also premiums; but that's only a minor detail.

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

Now, other "discounts" have been implemented, and required more columns,
but those have been orthogonal to the percentage/fixed amount. For instance,
we have later implemented an option to round prices to units after applying
a percentage. But this more to be seen as a attribute of a discount, than
a different discount type. The possibility that later on you may have to
implement a different discount type is a question that should be considered,
but ask yourself (and the rest of your business) how likely it is that such
a thing is going to happen, and how hard is it actually add a column. And it
may very well be that a different discount type requires new columns anyway
(because it requires a piece of data that isn't numeric, or it may require
 more than one piece of data).

Do note that your second option doesn't require a NULL. You could use
a third option "none" next to "percentage" and "euro". Or you could
put the "discount_type" and the amount in the second table, using the
same primary key as the original table. Not having a discount means no
corresponding row in the second table.


[1] We're in the hotel business. Think of a hotel giving a EUR 10 discount
    if you're staying alone in a room, and a 10% discount if you select
    a non-refundable rate.



Abigail