Peggy,
I can certainly sympathise with your struggle. The kind of thinking
required to work databases does not necessarily come naturally for the
normal person. Or most abnormal people for that matter.
I don't know of any model to direct you to, as such. But I will try to
outline a few ideas that will hopefully help you over the hump.
First idea, forget user-friendly when you are designing your tables.
That comes later. The table design is normally dictated by the real
structure of the real data. User-friendly doesn't come into it. Wait
until after you've got the tables right before even thinking about forms
and reports.
Second idea... this is one of the core principles of database design: If
a value can be derived/calculated from existing data, then
derive/calculate it, don't store it.
Apart from that, the key skill is recognising what are the actual data
elements you need, and what is the nature of the real-world
relationships between these elements.
This last point is what you are relating to when you say "I provide a
service. I bill for it. I get a payment. I have a balance." Fair
enough. So, one of the data elements is your Invoices. Your invoices
are issued to Clients, that is another data element. I'm just imagining
here, so correct me if I get any of this wrong as regards your real
operations. But assuming that any given Client can end up with more
than one Invoice, then you have a one-to-many relationship between
Clients and Invoices. Than automatically means that you need two
tables, with the primary key field of the Clients table represented by a
corresponding foreign key field in the Invoices table. Ok, then it gets
a bit fuzzy, depending on your actual practice. Is an invoice issued
for a single nominated amount, or can the invoice include more than one
chargeable item? If the first, then your Invoice table will have an
InvoiceAmount field. If the second, you will need another table for the
chargeable items, because there is a one-to-many relationship between
the Invoice element and the InvoiceItems element. And in this second
case, each Item has an Amount. Either way, you have all the information
you need in order to know how much money your client owes you. Then
they make Payments. Different people do this differently. Do you want
to simply credit a Payment received against the Client's record, or do
you want to tag the Payment against the Invoice that the client is
paying? If the first, you will need a ClientID foreign key field in the
Payments table. If the second, you will need an InvoiceID foreign key
field in the Payments table.
OK, now we can devise some forms where you can enter the Client
information, and the Invoice information, and the Payments received
information. Beautiful. Now you want data relating to Current Due,
Past Due, and Balance. You want this data for your purposes on forms
and reports. Have you already got this information? Yes! It is easily
obtained from the Invoice and Payments data. Do you need to enter this
data? No! Should you enter this data? No! These are
derived/calculated values, and should not be stored. All you need is a
couple of simple queries... the details of which will depend on your
actual business rules such as what defines Past Due, and on some of the
other factors that I mentioned above.
