Database Design Advice

[Dirk Koopman]

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 
predominant).

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.