Database Design Advice
Smylers at stripey.com
Fri Nov 8 11:43:08 GMT 2013
> 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
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.
The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more: http://civilsocietycommission.info/
Please sign this petition: http://38d.gs/1cJ9ViA
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND
More information about the london.pm