Advice needed on database design

[kyuball]

Hey all,

I wanted to post on how to set a module or some equivalent to do automatic billing, but I think that that would have become a longer than necessary thread if my database is not designed correctly in the first place. I thought I would solicit some feedback on whether my database can even be designed to do that. So here goes:

I have a very simple database based on four tables:

1) tblclients: Has very basic information such as names and such but the relevant data to billing would be the monthly fee field called "fee" that is set for each client (can range from 10 to 300 dollars.)

2) tblmonth: Gives each billing month a distinct entity/ id and has three fields - monthid (pk/ autonumber), month (the name/ title of the month, e.g. August 2009) and monthstart (an actual date field that sets the start of the month)

3) tblpayment: The table that loads actual payments made by clients and has, aside from actual money paid and date they made the payment, a tblclients.clientid linked field called "payclient" (who made the payment) and tblmonth.monthid linked field called "paymonth" (what billing month/ invoice they are making the payment towards)

4) tblfeetype: This one would probably be irrelevant as it just feeds a lookup field in tblclients that tells you what kind of fee they pay (e.g. utility reimbursement, section 8 balance, etc.)

I got the database set up well to input/ edit/ display client info and payments, but the final component would be a date triggered automatic billing module that would maybe append to another table or update query or something that would, at the start of the month, "bill" the clients (e.g. client X has no outstanding balnace on 8/31, but when the module triggers the billing month on 9/1, he would "owe" whatever his fee amount is) by perhaps adding a new record that has like... invoiceid (pk) monthid, clientid, amount owed, etc.... From there, I can set a query based on payments against a billng month vs. what he or she owes on that billing month and calculate their outstanding balance and what they have left to pay on each billing month?

Is the database even set up to do something like what I just described?

 

[Alisa]

Sounds like it would work to me.

 

[HiTechCoach]

I would say you are missing some tables. Where is the table for traking charges? This is what the payment should be applied to.

I would also say that you probably have some normalization issues. Without seeing your actual table structure, it is hard to say.

 

[Desafinada]

I agree with Hightechcoach. For one, if one client has more than one bill type to pay, you'll have more than one entry in the tblclients for the same person.

I'll assume one client can have more than 1 types of bill and that you care to differentiate between different bill types instead of having a lump sum. Another assumption is that a bill type is the same every month for one client but may differ from client to client.
I'll also assume a client can only pay for a full bill such that a bill is either fully paid or not paid at all.

My suggestion for the design:
tblClients
- clientID, name, address and so on

tblBills
- billID, clientID, billType, Amount

tblBilltype
- billType, Description

tblInvoices
- invoiceNo, billID, dateBill, boolPaid/Not, datePaid

Underlined are PK, italics FK
From what I read I don't think you need a tblMonth, a datefield in tblbills does the job. For your query I would generate monthly insert into tblInvoices for all billID available and date them with the new month. To find out who owes how many you could query the tblInvoices join tblBills for sum of all not paid invoices grouped by clientID

 

[kyuball]

Hey Desafinada,

Thanks for the input and sorry about responding so late despite all of your time(s) being donated to help (I was on vacation...)

Actually, each client has only one bill type to pay. When they enter the program, they have a an agreed upon fee and that is exactly what they owe each month (basically it's a mental health day program and their insurance situation determines what their monthly fee is and that never changes...) You are right that if a client has multiple fee types, they are entered separately into tbl clients, but that works out because those separate accounts tend to be contributions made by outside parties like attorneys and relatives that sometimes the client doesn't even know about and is definitely not even issued a receipt for (of course, we still keep track of it...) That is why I figured I can have a regularly updating bill/ charge type table since, like rent, their fee never changes and the numebr of times they owe that fee each month never changes.

Also, it is not the case that the client has paid the bill in full or not paid at all. We often deal with those with limited income and many of them tend to pay piecemeal. If for instance John owes $50 each month and he has been in the program for two months covering July and August of this year, he would owe 50 dollars each month and his total owed would be $100. However, he made three payments of $30 at various points throughout the three months. We would then record the payments and apply them the oldest billing month owed. I was hopin gto get a table or update query or something (to tell the truth, I am not sure where to even start....) that would keep track of the monthly billing, then add those up for each client against their payments and spit out a balance to view on a form that would show that John still owes 10 dollars on the August 2009 invoice...

Any help would be appreciated, and hopefully, I have not worn out the time limit on this thread by responding faster....