Database Design Advice
djk at tobit.co.uk
Fri Nov 8 12:54:32 GMT 2013
On 08/11/13 11:43, Smylers wrote:
> • 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.
It all depends on the application and your view as to where the business
might go in the future. A case that I had to deal with many years ago
was related to the building trade and their suppliers (Lloyds Insurance
Broking would also qualify [in a less complex way, and for other things]).
In essence suppliers would supply using "discount rate card"s which
would state that for products of discount band a1-z9 (to give a rough
scale of number of bands) would have chain discounts on "book" price of
the form +135% [...] -30% -10% [...] and customers might offered
anything from cost + 5% (say on a lorry load organised by phone) -> 5%
off list price (all retail customers [everyone likes a discount]), via
-30% "trade" -10% (for plumbing) -5% ('cos you're a good customer).
And then there would be fixed amounts off individual items (say in
sales) as well as normalising units of measure (timber is easier now,
but in those days there were about 6 units of measure - depending on
where the timber came from. Now there are "only" about 3 with m3 being
What I am trying to say is: do something that doesn't back you into a
corner that you hadn't anticipated.
Oh and normalising tables an all in databases is all very fine and gives
one interlecktuwal warm feelings, but it doesn't necessarily give the
*business* an optimal result.
More information about the london.pm