Hi - I have a design question and am wondering if this is a commonly
encountered issue and if it has a standard solution.

Quick version of question:
Free-lance business accounting database.
How to design to account for payments that apply to a specific job AND
payments that apply to an overall balance owed for more than one job. Do
I make 2 payment tables - one for Client payments and one for Job
payments? Or is there some other way?

More detailed version of question:
I have a database that I designed myself for my freelance business.
Essentially it boils down to three main tables - Clients, Jobs and
Payments. The relationship of clients to jobs is one to many (many jobs
per client). I related the payments table to the clients table (again
many payments per client). I did it this way rather than relate the
payments to the jobs table for the following reason: sometimes a client
will make a partial payment toward the over-all balance that he/she owes
for several jobs, so that the payment will not relate to any specific
job. More often, however, they will make a payment toward a specific
job. In that case I still apply it to the over-all balance. This
basically works, as the math works out the same either way. The problem
arises in the following scenario: let's say they have several jobs
pending and have previously made a partial payment toward job X. When I
want to invoice them again at a later date for job X, the job invoice
then does not reflect the partial payment. So, in this case, I have
invoice them for the particular job and also send along an invoice for
all jobs to show their payment. This is somewhat clumsy. Should I
create 2 payment tables - one for "Client" payments and one for "Job"
payments? This seems redundant. Are there others who have encountered
this and come up with a solution? I'm not an accountant by any stretch,
so maybe there is a standard accounting method to deal with this that I
am unaware of. Suggestions? Examples? Thanks all.

Fred Hickler a écrit :
Hi,

If you're working with Filemaker 7 or 8, I would recommend The Missing
Manual (http://www.oreilly.com/catalog/fmakerprotmm/). Chapter 7 and 8
cover relational techniques and the authors describe a database that is
almost precisely what you're trying to set up. Entities include clients,
jobs, expenses and timeslips, invoices and payments as well as (invoice)
line_items and invoice_payments. The last one, a join table, is the key
to solving your problem: it allows you to apply part of payments towards
different invoices, for instance applying as much as neccesary of a
payment to cover whatever remains to be paid on an old(er) invoice, and
the rest to a more recent one.

Also, I would make a distinction between an invoice and a "statement"
report (I don't know the proper expression in english, in french it's
"état de compte"), which could list invoices, invoices_payments that
were applied to each, and remaning balances (if any) as well as the
overall balance. Looking at it from the other side, you could also list
(recent) payments and show how they were allocated to various invoices.
That statement is not a new invoice, as you can see, but could be sent
along with any new invoice, or whenever a reminder is needed

As you can see, you may need new "entities" and corresponding tables to
get the flexibility that you want. For instance, (this is not discussed
in the aforementioned book), you may also need a job_invoice join table
if you sometimes want to send an invoice that cover many jobs as well as
billing for a single job through many successive invoices.

Hope that helps

Marc-André Paiement

Somewhat independently of the jobs/client issue is the fact that you need a
join table, as Marc-Andre describes in his response, between Payments and
whatever is being paid. Normally, 'whatever is being paid' would be
invoices. The join table, where there is one record for each application of
payment toward a receivable, accomodates the distribution of payments, so
that one payment can be applied to more than one thing (again, usually
invoices). The join record would hold, at the bare minimum, the serial
number of the payment, the serial number of the item being paid, and the
amount. Any other information, such as the date, who is paying, the payment
instrument, etc., can all be found in the related Payment record or the
record for what is being paid (usually invoices, to make a point).

In the conventional model, that's enough-- you can look from Clients to
Payments and get overall aggregates, from Clients to Invoices to get overall
amounts owed, from Invoices to the join table (let's call it
'Applied_Payments') for individual invoice balances, and-- and this is one
way to approach your problem-- Payments to Applied_Payments to see how much,
if any, of a payment has not been distributed. That last value represents
money paid 'in general'-- not appplied to any given invoice (the invoices go
back to Jobs, via the Job_Number).

But I don't really like that idea. I think payments should be entirely
distributed; unapplied balances typically represent refunds, credits,
over-payments. On the other hand, it doesn't sound so much as though you're
a company that has to adhere to common accounting methods, and you've not
mentioned needing to pass anything to Quickbooks or other accounting
software.

Still, people generally pay for something-- specific work done, even if that
work is a retainer or something else vague.The join table resolves partial
payments and payments to more than one invoice, even if they are, in turn
partial payments. I do think you'd be better off with that tried and true
model.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634

Certified For Filemaker 8
Certified For Filemaker 7


"Fred Hickler" <fred (AT) fisheyeworld (DOT) com> wrote

Wow - thanks for the help. I've been learning FMP while slowly building
and using this database. It looks like it's time to overhaul it using
these techniques or maybe even start over from scratch. To be honest I
don't completely understand the "join table" concept yet, but at least I
know what direction to go. I just ordered that "missing manual" book
too, so I should be good to go. Thanks again.

A join table-- often called a junction table in the SQL world-- does just
what it says-- it joins two other tables in a many-to-many relationship. The
easiest example is invoice line items, which joins the invoice to an
inventory item. An invoice line item record needs two fields-- the serial
numbers (or 'ID' or 'foreign key'-- some form of unique identifier) for the
inventory item and the invoice. Once it has those two pieces of information,
it can figure out, by going through the invoice back to the customer, who is
being invoiced. By looking at the invoice, you get the invoice date and, if
there are separate tables for them, the order number and packing slip info.
By going the other way-- back to the inventory table-- you can look from the
inventory table at the invoice line items and see how many widgets have been
sold, or packed, if you look at the packing slip line items, or just
ordered, if you look at the order line items.

Another common example is class registrations in a school, joining classes
to students, where each class has more than one student, and each student
has more than one class. The join table will have the student ID and the
class ID.

Hotel rooms joined to customers via a 'Bookings' join table.

Donors joined to charities via a 'Donations' join table.

Staff joined to calendar dates via a 'Tasks' join table.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"Fred Hickler" <fred (AT) fisheyeworld (DOT) com> wrote

I was thinking about this as I was trying to fall asleep last night and
had a database epiphany. Perhaps what I want is the following:
clients->Jobs->invoices<-payments
where clients to jobs is one to many and jobs to payments is many to
many via the invoices join table. Would you guys agree with that?

In article <12khjvrq577i30 (AT) news (DOT) supernews.com>,
"John Weinshel" <john (AT) datagrace (DOT) biz> wrote: