> Apologies to the list if this is off-topic. We'll continue this
> privately if that's the case.
>
> > That depends on the requirements.
> > Care to tell em?
> Simple invoice data model. Each line item has an item_id (of course), a
> qty, a unit_price, and a discount. The discount can be a fixed amount
> (i.e. $10.00,-) or a percentage (i.e. 10%).
>
> There might be some editing to 'finalize' the unit_price as long as the
> invoice is still 'open'. A discount percentage need to be retained so
> that the percentage value can survive changes to the unit_price.
>
> For example :
>
> item_id qty unit_price discount subtotal(calculated)
> =========================================================
> 001 5 $1.00 $0.50 $4.50
> 002 10 $2.00 20% $16.00
> =========================================================
>
> The $4.50 figure might seem wrong if you interpret the 'discount' as
> 'discount per unit'. In this case, the discount was applied to the
> subtotal.
>
> At a later date, the unit_price might be modified (see the second line
> item below) :
> item_id qty unit_price discount subtotal(calculated)
> =========================================================
> 001 5 $1.00 $0.50 $4.50
> 002 10 $2.50 20% $20.00
> =========================================================
>
> That's all there is to it, actually. There was never a formal
> requirement on this, so i hope i explained it clearly enough. Of course,
> there is another 'discount' applied to the master level (INVOICE) that
> works similarly.

Well then, the purist approach would be to have no NULLs at all,
but a simple approach could be:

item_id, qty, unit_price, discount_perc, discount_amount, subtotal
(calculated )

Then, add a check constraint:

(discount_perc is null and discount_amount is not null)
or
(discount_perc is not null and discount_amount is null)
or
/* no discount? */
(discount_perc is null and discount_amount is null)

probably a shorter way would be:
not (discount_perc is not null and discount_amount is not null)

The subtotal could be a "computed by" field saying:

(qty * unit_price) /* to get the total amount */
- coalesce (discount_perc / 100.0 * (qty * unit_price), discount_amount )


This way, a value in the column means exactly one thing.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com