Results 1 to 11 of 11

Thread: Debtor System Design

  1. #1

    Thread Starter
    Registered User
    Join Date
    Oct 2018
    Posts
    1

    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.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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!

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Debtor System Design

    Quote Originally Posted by Zvoni View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Debtor System Design

    Quote Originally Posted by techgnome View Post
    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

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Debtor System Design

    Quote Originally Posted by Zvoni View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Debtor System Design

    Quote Originally Posted by FunkyDexter View Post
    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

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  11. #11
    Fanatic Member
    Join Date
    Jun 2019
    Posts
    557

    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
  •  



Click Here to Expand Forum to Full Width