Database Design Advice

[Smylers]

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.

Thanks

Smylers
-- 
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