-
Sep 28th, 2021, 01:15 AM
#1
Thread Starter
Registered User
Debtor System Design
I have been asked to design a point of sale system with support for debtors. Each month statements will be produced showing current invoice summary and an aged summary for 30, 60, and 90 days owing.
I am unsure as to the best approach.
I could maintain a end of month account that has the aged debts and adjust these as payments and sales are made. I could age the debts on the first transaction of the month so that current becomes 30, 30 becomes 60 etc. I could reduce the totals with payments and mark each invoice as paid upon receipt. The issue arises that if there is a balance in April, for example, and no transactions for May, how do I age the balances for May statement as all balances will be older.
The alternative is to mark invoices are paid on receipt and then run SQLs against accounts with a balance owing and get the total of debts by age and update an end of month account and print the statements.
It is also a requirement that the statement can be reproduced if required in case the customer's copy is lost.
I could use both where the aged balances are updated via each transaction and run an end of month procedure for the accounts that have not been updated in the current month. If the customer file has a last contact date in the previous month then only these need to be aged.
Is the an alternative?
There are four terminals and they are updating from a DOS system. Although there are many sales there are probably only about 100 accounts that are credit as the others are all cash or card.
I intend to use VB.
Any advise would be appreciated.
-
Sep 28th, 2021, 07:32 AM
#2
Re: Debtor System Design
There is a lot to unpack there and I can't help. I'm posting because it looks like you joined in 2018 and this is your first post
Please remember next time...elections matter!
-
Sep 28th, 2021, 09:19 AM
#3
Re: Debtor System Design
There are some issues with the model described.
First off: Use a separate table for issued invoices with all fields pertaining to the invoice (Yes, it's redundancy, but e.g. a sales price can change within your time-window, and a price is "fixed" at the point of sale)
This is for your "reproduce the invoice".
Next: The 30, 60, 90 days due.
It has to be clear, that e.g. an invoice which is due since 65 days, cannot appear neither in the 30 nor in the 90 days category
Yes, you could argue, that if an invoice is due since 65 days, then it's also due since at least 30 days, but that's not how it works.
Bottom line: "create" the summaries during runtime querying payment-date against "Today"
e.g.
An invoice is created on Sep. 28th 2021 with Terms of Payment 30 days net, meaning: Payment due is Oct 28th 2021
If on Nov 28th 2021 that invoice is still not paid, it falls into the 30 days category
As for storing the summaries:
Don't! Or at least: It depends.
If it's like i described above you have a very dynamic dataflow, since it can happen any day an invoice slips from 30 days into the 60 days category, then IMO it's easier to create the balances on the fly.
If you don't have so much dataflow, then it would boild down to some fields in the client-record:
BalanceTotal, Balance30, Balance60 and so on....
Could be even done via Triggers (Invoice created --> add to balance, Payment received --> subtract from balance)
A pitfall is, that you have to differentiate between payments regarding a specific invoice, or a blanket payment, in which case you would have to go "backwards" through the unpaid invoices (the longest overdue first and so on.)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 28th, 2021, 10:44 AM
#4
Re: Debtor System Design
Originally Posted by Zvoni
As for storing the summaries:
Don't! Or at least: It depends.
If it's like i described above you have a very dynamic dataflow, since it can happen any day an invoice slips from 30 days into the 60 days category, then IMO it's easier to create the balances on the fly.
Even if there isn't much dataflow... I'd still go with this option. Not only will it scale better, but you avoid some of the issues with triggers (which tend to be somewhat hidden and very black-boxy)
On a related note - as someone who has had to deal with this kind of stuff before - read up on accounting principles in general... not as a programmer though, but you may even find that "Intro to Accounting" is beneficial. Otherwise you'll end up with something that may make sense from a programmer, but from a business it doesn't, and in the end makes you life harder.
-tg
-
Sep 29th, 2021, 02:28 AM
#5
Re: Debtor System Design
Originally Posted by techgnome
Even if there isn't much dataflow... I'd still go with this option. Not only will it scale better, but you avoid some of the issues with triggers (which tend to be somewhat hidden and very black-boxy)
On a related note - as someone who has had to deal with this kind of stuff before - read up on accounting principles in general... not as a programmer though, but you may even find that "Intro to Accounting" is beneficial. Otherwise you'll end up with something that may make sense from a programmer, but from a business it doesn't, and in the end makes you life harder.
-tg
tg, correct.
I just mentioned it for completeness sake.
What's non-negotiable for me IMO is the separate table with the invoices, because this data is "fixed" (unchangeable), and all queries would run against this table
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 29th, 2021, 06:56 AM
#6
Re: Debtor System Design
Originally Posted by Zvoni
tg, correct.
I just mentioned it for completeness sake.
What's non-negotiable for me IMO is the separate table with the invoices, because this data is "fixed" (unchangeable), and all queries would run against this table
Completely agree... I'd go so far as to suggest a table (or set of tables) for Invoice ... and another set for the AR (Accounts Receivable) side of things. All aging and debits/credits are tracked in the AR tables.
-tg
-
Sep 29th, 2021, 07:16 AM
#7
Re: Debtor System Design
tg,
yeah, that's the way you usually do it.
You'd probably need a child-table to AR for partial payment of a specific invoice, since usually you're obligated to track every single transaction (Date, amount, Reference etc...)
EDIT: Or if you have a separate table for Transactions to link it up to the AR-Table with AR being Master
EDIT2: *growl* just after thinking about it, you would even need a m:m relation between AR and Transactions. For the blanket payments.
One blanket payment is one transaction, but can influence many invoices.
As well as the other way round: one invoice might be paid with several transactions
Last edited by Zvoni; Sep 29th, 2021 at 07:21 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 29th, 2021, 08:43 AM
#8
Re: Debtor System Design
I'm concurring with the answers here.
Your invoices table should include the date it was issued, it should not include the age of the invoice. The age of the invoice today will be wrong tomorrow, it's ever changing. The date of issue is unchanging and the age at any point in time can be calculated from it.
As for copy invoices - I might... might... keep a table of them but I would always view it an an archive. It is not part of your transactional data and shouldn't be view as such.
Honestly, all you really need to support this is a single table of invoices and receipts. It's likely that you would split those into separate tables as they're likely to contain a slightly different set of fields but it's not necessary from the information you've given here. Either way, everything else is calculable from this basic data.
A couple of golden rules that apply to you:-
1. Never store the same piece of data twice
2. Never store a piece of data that you can calculate from the data you have stored
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Sep 29th, 2021, 08:57 AM
#9
Re: Debtor System Design
Originally Posted by FunkyDexter
As for copy invoices - I might... might... keep a table of them but I would always view it an an archive. It is not part of your transactional data and shouldn't be view as such.
That's actually a way better word to describe an invoice than my "unchangable"/"fixed"
There is still one reason why i would still consider (partial) redundancy for the invoice-table:
Changing of Name and/or address.
At least, in Germany it's allowed to keep your client-number as long as your VAT-ID doesn't change.
I know it for a fact since the company i work for stepped into this Tax-Trap!
It's valid for both sides of the equation: creditors (suppliers) and debitors (clients).
As long as they don't change their VAT-ID they can change names, address, dirty socks, whatever.
e.g.
In January i issued an invoice to "client-no 12345 - Mary Smith"
In March she changed her name (married) but keeps her client-no (since VAT-ID stays the same)
Now in September she requires a reprint of her January-Invoice.
If you don't have redundancy, your reprint would read "client-no 12345 - Mary Wesson".
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 29th, 2021, 01:19 PM
#10
Re: Debtor System Design
If I was being REALLY purist I'd say you should cope with that by recording dated name changes in a table of it's own but, yeah, I'd be being overly purist at that point.
I generally take the view that, if you want to record some entity for later retrieval in an immutable form then an archive table is generally the best way to do it. Strictly speaking it breaks Third Normal Form but then so do nulls so all rules are made to be broken.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Sep 29th, 2021, 02:22 PM
#11
Re: Debtor System Design
Simple event sourcing is very good case for such systems. But it is complex at the beginning and may require to create own library to handle events as most public libraries are overcomplicated for small apps or are just demos that need a lot of rework to get them working.
Event sourcing in systems design comes from accounting. There are many reads and videos on the net but most of them are way too far from the real implementation.
In general the idea is simple: all information is saved as "events" in a log. Not programming language events like "button click" but real life events for your specific domain: payment received, invoice paid, invoice created, invoice sent, etc. There the real accountants should help you how they do the job.
For each of these events you can write event handlers that do process the data. For example if you start with balance=0.00 and you get 3 payments and you paid 4 invoices for specified month. On each of these events you calculate the current balance. On payment received you add to balance and when you pay something - substract from balance.
There is small problem that you can handle programmatically if you use SQL database. Events are having different structure and fields as they are related to different topics. SQL databases were not designed for different structures but you can simply store serialized event info into single field and de-serialize in your app on reading.
The events are stored in append-only SQL table: event ID (or better GUID), event date/time, created by, event channel, event name, serialized event info (e.g. nvarchar(max) field in MSSQL server if JSON will be used for serialization).
You can query events by date/time period and by event name. Sometimes event channel (topic or whatever term someone may use) can limit some events to specific topic, e.g. users management is different from invoicing and payments so different channels can be used to query all events in specific channel for specified time using simple SELECT. Then by event name deserialize to proper object and invoke event handler that will process the event.
Sample events (more pseudo-codeish than real code):
Code:
public class PaymentReceived
Company as string, VATNumber as string, amount as Decimal, DateReceived as DateTime
end class
public class InvoicePaid
ReceiverCompany as string, VATNumber as string, amount as Decimal, DatePaid as DateTime
end class
And event handlers:
Code:
private _balance as Decimal = 0D
public sub Handle(ev as PaymentReceived)
_balance += ev.Amount
end sub
public sub Handle(ev as InvoicePaid)
_balance -= ev.Amount
end sub
You can use database operations of aggregate balance in above example instead of variable. You can simply re-execute events for specified period (remember to set balance to zero or to previous known value) and check if value stored in database is correct.
In Event Sourcing this is called re-playing of events and most financial systems re-play every night events for the day, when the month is changed for the previous month and same for previous year. As the event log is append-only you can re-create your current view in the database at any time.
So if you have event InvoiceCreated with all required data inside, you can just find this event (you already know the receiver company, date issued, etc.) and you can de-serialize event information and print again. No need to keep history tables. You can use history tables or other views to speed up current queries, but when audit is required - you replay everything from the event log and get the correct information.
If you get to the place where event sourcing sounds reasonable for your system, then you can check CQRS :-) But better not to add more messy design patterns. At the beginning just play on sheet of paper (or text file) with events and see if that fits your case.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|