September 27, 2007 at 6:09 am
Hi all, I'm new here so first of all hello 🙂
Just want to get your views on this application design.
It is a despatch and accounts app. I have to deal with payments which will settle any number of outstanding invoices for a particular client. I also have to deal with overpayments.
I have an invoice and invoiceline table. I was thinking of adding a payments table with amount and clientid. I would then add a invoicepayment table with the following fields:
invoicepayments
invoicepaymentid
paymentid
clientid
invoiceid
amount
Any overpayments would simply be an entry into the invoicepayments table with no invoiceid and so I would know that this is money which is credit to the client.
Anyone have any views on this? Or is there an already established way of doing this?
Thanks,
Stephen.
September 27, 2007 at 6:39 am
I would suggest you look at a couple of accounts payable systems. They will often be designed poorly, but you should be able to get a feel for the problems you will run into. If you have an accounting department or someone handling these invoices and payments now, spend some time with them to make sure you undeerstand their process.
I can tell you that your design does not account for a payment that is for multiple invoices. I have seen that problem a lot.
Most good AR/AP systems allow you to (typically automatically) apply receivables in parts to one or more invoices rather than attaching the receivable directly to the invoice.
September 27, 2007 at 6:54 am
Hi Michael - thanks for the prompt reply 🙂
I have sat in accounts for a few days and can see how they operate. They post a payment and then go on to the next screen to allocate this payment to 1 or many invoices. They can part pay an invoice on this screen also.
My system would allow a payment to apply to many invoices (many paymentids in the table invoicepayments to each invoiceid it pays)
I can see the way it works - I'm just having a problem commiting to a specific design and architecture...
September 27, 2007 at 7:09 am
Your InvoicePayments table has an InvoiceID in it suggesting that a payment record could not be for more than one invoice. It could just be that I don't have enough information.
If the association between invoices and payments is not necessary, you may want to consider having all of your invoices accumulate against a customer account, and all payments balance against this account. I have seen this done a few times and it really simplifies things. Sometimes convincing an accounting department is difficult through.
Make sure you ask yourself if you sure you really want to build this. AR/AP systems can get pretty complicated and you may find yourself building one as you go down this road. If you already have one, you may want to consider feeding invoices and payments into it.
In my opinion, a good developer tries to write as little code as possible.
September 27, 2007 at 7:52 am
Michael - sorry - my table schema there was a bit wrong - I'll have 3 tables:
invoices
invoiceid
paid
payments
paymentid
amount
invoicepayments
invoicepaymentid
paymentid
invoiceid
clientid
amount
The user can tick off invoices on the asp.net payment page or just put in a part payment. On processing, I will programatically compare the payments with the invoice totals. If the total matches, I will mark this invoice paid. If the total doesn't match, it will be part paid. If there is any amount outstanding, it will go in the invoicepayments with no invoiceid. i can look up these values later when I need to. I'm sure it will change slightly when I put it in to practice but that's my quick mock up...
Unfortunately they want an all in one package so I have to go down this route...
September 27, 2007 at 8:08 am
That looks ok to me. I would still take the time to look at a couple of finance systems. A few years back, I worked for a company that wrote a financial system and this was similar to what they had done.
Watch out for posting and undo functionality. At some point, you will want to lock records from being changed, but users will need some way to fix mistakes before records get locked.
Dealing with overpayments will be a bit complex if you leave the invoiceid null - especially if you want to give a refund for an overpayment later or allow an overpayment to stand and be applied to another invoice in the future.
Good luck.
September 27, 2007 at 8:29 am
I'd really storyboard these scenarios with a CPA/CFO type person and see what they think. I'd hate to leave the invoiceID null. It should be linked and then you might have a negative (debit?) balance for the invoice.
Lots of systems don't allow changes. Instead they post additional entries to he account. So you might see a payment (miskeyed) for $10.00 and then a credit back for $10.00 with an immediate debit of $100 (the correct amount).
September 28, 2007 at 3:14 am
Does your organisation use an accounts package? This type of thing should be covered by the package.
There are significant business risks in organisations writing their own accounts systems. Internal and external auditors and ergulators place less confidence on home-built systems than packages that are known to meet regulatory guidelines. You should look at how these issues will be tackled before making a significant investment in building an in-house accounts system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply