How about a data model?

http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm



--
Douglas von Roeder
949-336-2902

On Fri, Oct 14, 2016 at 11:58 AM, Wayne Stewart <wayne.b.stew...@gmail.com>
wrote:

> Hi,
>
> I'm not an accountant so this probably is totally wrong but what I do is
> this.
>
> I have 4 tables involved
> Client (fairly obvious)
> Consultations (a consult is an invoice effectively)
> Payments
> Transactions.
>
> The transaction table links to the other three.  It's fields:
> ID
> Client_ID
> Consult_ID
> Payment_ID
> Type
> Date
> Amount
>
> If the transaction is for a consult then type = 1, payment_id is blank and
> the amount is the same as the consult amount.
>
> If the transaction is for a payment then type = 2, client_id is blank and
> the amount is the negative of the payment amount.
>
> Client balance is the sum of transactions
> Payments are applied against the total rather than individual invoices.
> - no need to unlock invoice
> - clients can pre-pay
>
> Does that make sense?
>
> Wayne
>
> On Saturday, 15 October 2016, Chip Scheide <4d_o...@pghrepository.org>
> wrote:
>
> > Theory says:
> > do not store something that can be calculated.
> > Practicality often says otherwise
> >
> > line art structure:
> >
> > [invoice] <- [line items] <- [payment_lineitem_link] --
> >           <- [payments] <-----------------------------|
> >           <- [addresses]
> >           -> [Purchaser]
> > Entry/listing form shows Sum([Line items]) - sum([payments])
> >
> > for reporting
> > Selection to array (various data)
> > process report by pulling data from arrays
> >
> > You never have to worry about :
> > - adding a payment - create a new record with the right invoice ID
> > - adding a line item - create a new record with the right Invoice ID
> > - current outstanding balance (read only) Sum([Line items]) -
> > sum([payments])
> > - current total (read only) Sum([Line items])
> >
> > you will rarely have to worry about :
> > - applying a payment (tracking against which line item(s) a payment
> > covers)
> > - deleting a line item (only an issue if done outside of an entry form)
> > - modifying a line item (only an issue if done outside of an entry form)
> > These are situations where 1 user may have locked a line item or
> > payment while another user is trying to change something. If the
> > changes are only allowed to be done while in an entry form then even
> > these go away, as if the parent record is locked the related should be
> > too (read only).
> >
> >
> >
> >
> > On Fri, 14 Oct 2016 09:20:05 -0700, Kirk Brooks wrote:
> > > I'm going to fork this thread into a separate discussion because I
> think
> > > it's a good topic and way far afield of the OP.
> > >
> > > I have never been at a point in my development career when I haven't
> had
> > at
> > > least one project that had invoices involved. Most of my databases had
> > the
> > > 'classic' structure of an invoice table and a line items table. You add
> > > line items, sum up them up and write the totals into the invoice
> record.
> > > Fine until you start trying to track payments also. Hmm. So now I add
> > > another couple of fields for 'payments' and 'balance due'. But now
> it's a
> > > de-normalized situation: the sum of line items and sum of payments may
> > not
> > > be so static. What if a payment is being applied but the invoice record
> > is
> > > locked? I can't update the invoice so does that mean the payment can't
> be
> > > entered? What do we do with the payment? And so on.
> > >
> > > Lot's of high end accounting databases started dealing with invoices by
> > > looking up the values when needed. ie.
> > >
> > > Invoice_get_total(invoice id) = sum Invoice Lines
> > > Invoice_get_balDue(invoice id) = sum Invoice Lines minus sum Payments
> > >
> > >
> > > ​I tried that approach in 4D years ago and it was just too slow to be
> > > feasible when building reports. Or maybe I wasn't as good at setting up
> > > structures back then. It is undeniably faster to write reports from
> > static
> > > data. But in a dynamic environment (where someone is literally watching
> > an
> > > invoice for the balance due field to change) it gets more complicated.
> > >
> > > Another problem with the lookup approach is historical data. You spend
> a
> > > lot of computer resources looking up static data that hasn't changed
> in a
> > > long time and really won't ever change again. That's not what you want.
> > >
> > > My approach to invoices currently is this:
> > >
> > >    - ​There are 'active' and 'inactive' invoices. Inactive ones have
> the
> > >    totals written to the record. Active ones don't.
> > >    - ​Invoice records are never opened in a user window. I make a
> dialog
> > of
> > >    the invoice data but not the actual record.
> > >
> > >    - Changes are submitted as variables, submitted to a single method
> for
> > >    validation and updating the record.
> > >
> > > ​I think this gives me the best of both worlds. A pref lets the user
> > define
> > > how long to leave an invoice in the 'active' state after it's paid. A
> > month
> > > or two is a usually enough. Plus, it's not like this is a one-time only
> > > designation. An invoice can be changed from active to inactive and back
> > as
> > > needed, for some reason.
> > >
> > > ​Getter methods look at the invoice record to know where to get the
> data.
> > > Like so:
> > >
> > > // Invoice_get_total(invoice id)​
> > > ​if(invoice_active)
> > >
> > > $0:=  lookup the line items
> > >
> > > else
> > >
> > > $0:=[invoices]total
> > >
> > > end if​
> > >
> > >
> > > ​Editing invoices is restricted in various ways but the basics are
> pretty
> > > much anyone can view the record, you can submit changes if you have
> > > permission and those changes are managed by a central method.
> > >
> > > The final part is a background method that loads on startup, runs once
> a
> > > day and looks for paid invoices that can be made inactive. ​
> > >
> > > This is all probably overkill for single user applications. In busy
> > > multi-user dbs, where people creating invoices aren't necessarily the
> > > people entering payments or where payments arrive from external
> systems,
> > > this is a pretty good approach.
> > >
> > > On Fri, Oct 14, 2016 at 7:57 AM, Chip Scheide <
> 4d_o...@pghrepository.org
> > <javascript:;>>
> > > wrote:
> > >
> > >> Invoice, and invoice item
> > >> - Invoice item is changed, which changes a total on the invoice
> > >> both are locked to other users, until the transaction is completed.
> > >>
> > >> On Fri, 14 Oct 2016 16:49:10 +0200, Arnaud de Montard wrote:
> > >>>
> > >>>> Le 14 oct. 2016 à 16:11, Chip Scheide <4d_o...@pghrepository.org
> > <javascript:;>> a
> > >>>> écrit :
> > >>>>
> > >>>> yes.
> > >>>> But then :
> > >>>> - if the 1 user changed a value, everyone probably needs to know
> that
> > >>>> the record is being modified and whatever value(s) are displayed may
> > >>>> not be correct.
> > >>
> > >
> > > --
> > > Kirk Brooks
> > > San Francisco, CA
> > > =======================
> > > **********************************************************************
> > > 4D Internet Users Group (4D iNUG)
> > > FAQ:  http://lists.4d.com/faqnug.html
> > > Archive:  http://lists.4d.com/archives.html
> > > Options: http://lists.4d.com/mailman/options/4d_tech
> > > Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com <javascript:;>
> > > **********************************************************************
> > **********************************************************************
> > 4D Internet Users Group (4D iNUG)
> > FAQ:  http://lists.4d.com/faqnug.html
> > Archive:  http://lists.4d.com/archives.html
> > Options: http://lists.4d.com/mailman/options/4d_tech
> > Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com <javascript:;>
> > **********************************************************************
>
>
>
> --
>
> Regards,
>
> Wayne
>
>
> [image: --]
> Wayne Stewart
> [image: http://]about.me/waynestewart
> <http://about.me/waynestewart>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **********************************************************************
>
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************

Reply via email to