CODEWITHSUNDEEP
databasedatabase-designinvoicediscount
redmoon7777
redmoon7777

Reputation: 4526

Invoicing database design

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 ?

Upvotes: 3

Views: 1836

Answers (3)

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 7

markdueck
markdueck

Reputation: 43

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.

Upvotes: 2

Pelshoff
Pelshoff

Reputation: 1464

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.

Upvotes: 2

Related Questions