3

I created an application few days ago that deals with invoicing. I would like to know how to best integrate a discount to my invoices. Should I put it as a negative item (in the invoice_items table) or should I create a "discount" column in the invoice table ?

redmoon7777
  • 4,498
  • 1
  • 24
  • 26
  • What consequences would either of those choices have for you down the road? For example, would you like to have multiple discounts, or very specified discounts later on? – Pelshoff Dec 28 '11 at 20:13
  • thank you for the quick reply. there will be only one discount per invoice. – redmoon7777 Dec 28 '11 at 20:25
  • 1
    If there will only be one discount per invoice, then I wouldn't make it any more complicated than need be. – Pelshoff Dec 28 '11 at 20:27
  • so where should I put it? on the invoice table or add it as a negative item? – redmoon7777 Dec 28 '11 at 20:29
  • 1
    Oh sorry for being unclear. In my opinion it's easier and clearer to have it in the invoice table - having it as a negative item will make the processing of items more difficult, I think. – Pelshoff Dec 28 '11 at 20:30
  • @Pelshoff: I suggest submitting your comments as an answer so that redmoon7777 can accept it as correct - which, in total, they are. While there will be a single discount across the entire invoice, the information belongs in the invoice header. If the rules change later so that different discounts are applied to different items on a single invoice, then the discount can be stored in the invoice items table - either instead or as well. ('Instead' means you'd store a discount with every item; 'as well' means you'd store a discount for the invoice, but could override that per item as needed.) – Jonathan Leffler Dec 28 '11 at 22:57

3 Answers3

7

I would have it as a negative-valued item. The reasons are:

  • With invoicing, it's very important that the calculated value remains contant forever; even if your calculation formula later changes, you can correctly reproduce any given invoice. This is even true if the value was incorrectly calculated at the time - it was what it was.
  • Having a value amount means that manual adjustments for exceptional circumstances is easily handled - eg, your marketing manager/accountant may decide to give a one-off discount of $100 because of a late delivery. This is trivial with negative values - just add another row, but difficult/hassle with discount rates
  • You can have multiple discount amounts per invoice
  • It's totally flexible - it has its own space to exist and be whatever it needs to be. In fact, I would make the discount another "product" (maybe even multiple products - one for each distinct discount reason, eg xmas, coupon, referral, etc.
  • With its own item, you can add a reason description just like any other "product" - eg "10% discount for paying cash" or whatever
  • You don't need any special code or database columns! Just total items up as before and print them on the invoice. "There is no spoon (discount)": It's just another line item - what could be more simple than no code/db changes required?
  • Not all items should be discounted - eg refunds, returns, subscriptions (if applicable). It becomes too complicated and it's unnecessary to represent the business logic of discounts in the database. Leave the calculation etc in the app code, store the result in the db
  • Having its own item means the calculation can be arbitrarily complex. This means no db maintenance as the complexity grows. It's a whole lot easier to maintain/alter code than it is to maintain/alter a database
  • Finally, I successfully built an invoicing system, and I took the "item" approach and it worked really well
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 2
    +1, but only just. There are a lot of good points raised here, but I'm not sure I agree with the whole conclusion. It may depend on the industry where the invoicing is being done. The stability of the invoice is a key point; that would probably be handled by ensuring that the price data (in particular, but also product descriptions, etc) are accurate as of the time of the invoice (probably by copying relevant data from the product tables into the invoice items table, or something similar). Having 'special product codes' for things like a one-off $100 discount is fine. [...continued...] – Jonathan Leffler Jan 01 '12 at 02:50
  • [...continuation...] However, I think that handling discounts solely by special items is wrong; there is usually a discount for the item, and that can be recorded, as a percentage or an amount, with the item. If there's an overall discount, that can be recorded with the main invoice. In both cases, there's no need for a NULL to record the absence of a discount; the value zero is normally the appropriate default (unless, indeed, list price is the exception). – Jonathan Leffler Jan 01 '12 at 02:52
  • @JonathanLeffler You've raised good points. If there's a lot of discounting, then maybe you're right. However, when columns are defined but rarely used, ie when discounts are not the norm, you end up with "sparse" data, which is a design "red flag" for me. – Bohemian Jan 01 '12 at 10:05
  • Fair enough - 'industry where the invoicing is being done' is approximately equivalent to 'if there is a lot of discounting'; it means that the decision should be taken in the light of the actual usage. I suspect a general-purpose accounting package (one that could be customized to many different systems) would allow all the options we've outlined since I believe they are all used in different sections of the accounting world. – Jonathan Leffler Jan 01 '12 at 15:47
2

What consequences would either of those choices have for you down the road? For example, would you like to have multiple discounts, or very specified discounts later on? If there will only be one discount per invoice, then I wouldn't make it any more complicated than need be. In my opinion it's easier and clearer to have it in the invoice table - having it as a negative item will make the processing of items more difficult, I think.

Pelshoff
  • 1,464
  • 10
  • 13
  • 1
    I agree with your answer, I was about to do that when the client decided that he might want to have multiple discounts down the road. And therefore I had to use the item approach. – redmoon7777 Jan 02 '12 at 04:22
2

I fully agree with making it as simple as possible, but one thing to consider is if any item should be exempted from the discount? In that case you need to add a bool field in the details to remember which line should have discount.

Peter O.
  • 32,158
  • 14
  • 82
  • 96
markdueck
  • 43
  • 7