"cycles or multiple cascade paths" in Design

[Rainlover]

You should review your design.

It is correct to have tblClients (Why do you need the word Master)
It is correct to have tblInvoices linked to ClietID as you have.
It is correct to have tblReceipts. (Why do you need the word Payments)

Get rid of tblPaymentsReceiptsInvoices. The Table Name is a bit big for me. Anything done here can be taken up in other tables.

Now we get a bit tricky. Hang a Table off tblInvoices for Allocations. This would be like a Journal Entry. Credit Receipts and Debit tblAllocations.

Accounting gets a bit confusing at times and I have replied without proper testing.



Please post back with anything I can help with.

tblAllocations

AllocationPK AutoNumber
InvoiceFK Number
AllocationDate
AllocationValue (Does not have to be the same as the invoice value)

Because you are dealing with other peoples money it would be good to lock the records after each entry. If you make a mistake reverse it and do it again.
Don't forget this is a double entry so you must make the opposite entry in tblReceipts.

[Rainlover]

I've never built one like that but I've never had to build an accounting database. I have used QuickBooks.

Why do payments have to be associated with specific invoice?

Why not just record invoice and payment transactions on account and the net difference is account balance?

June

This is a common thought among people with no actual Accounting experience.

There are reasons like disputes where a customer will pay a recent invoice but not one that is under dispute. Rebates is another reason. They might get a special discount for paying within 7 days instead of the normal 30 days.

Sometime a credit has been issued to the client which they can apply to any invoice as the original for which the credit was issued against has been fully paid. If you don't know where to allocate then contact the client before you do anything else. The money can be left as a credit in the receipts table.
It is therefore important for the Debtors Clerk to allocate as per the Client's remittance advice.

What you are attempting to do is to match your records with the Clients. It is not much fun chasing a bad debt if the invoice is shown as paid.

Hope this brief explanation helps.

[June7]

I did work in AR and AP for several years. Our accounting program (Great Plains) did not relate payments to invoices, just calculated net balance. Credits (or other adjustments) were just more transactions on the account. However, we did not have to apply discounts on invoice for payment terms (like 10% net 15). GP did somehow calculate the age of balance due so that late fees could be applied. My point is when we entered remittances we did not have to specify invoices to apply.

[recyan]

You should review your design.
It is correct to have tblClients (Why do you need the word Master)
It is correct to have tblInvoices linked to ClietID as you have.
It is correct to have tblReceipts. (Why do you need the word Payments)

Accepted.

Get rid of tblPaymentsReceiptsInvoices. The Table Name is a bit big for me. Anything done here can be taken up in other tables.

Now we get a bit tricky. Hang a Table off tblInvoices for Allocations. This would be like a Journal Entry. Credit Receipts and Debit tblAllocations.

Accounting gets a bit confusing at times and I have replied without proper testing.

Please post back with anything I can help with.

tblAllocations

AllocationPK AutoNumber
InvoiceFK Number
AllocationDate
AllocationValue (Does not have to be the same as the invoice value)

Because you are dealing with other peoples money it would be good to lock the records after each entry. If you make a mistake reverse it and do it again.
Don't forget this is a double entry so you must make the opposite entry in tblReceipts.

Couldn't grasp it at first glance. Will put my head down to this & let you know how it goes.

Thanks

[Rainlover]

I did work in AR and AP for several years. Our accounting program (Great Plains) did not relate payments to invoices, just calculated net balance. Credits (or other adjustments) were just more transactions on the account. However, we did not have to apply discounts on invoice for payment terms (like 10% net 15). GP did somehow calculate the age of balance due so that late fees could be applied. My point is when we entered remittances we did not have to specify invoices to apply.

June

I don't know how to reply to that. I have had a bit to do with Great Plains and I know I could ask the Lady in Accounts Receivable and she would produce a report which showed some invoices as partly paid. However I think GP came in Modules. The more features you wanted the more you paid.

It doesn't really matter because what I wrote is still correct even if you used MYOB.

[Rainlover]

Accepted.
Couldn't grasp it at first glance. Will put my head down to this & let you know how it goes.

Thanks

I did say accounting can be a bit tricky. Especially when you put Money in the Bank and you have to Debit your Assets.

Post back when you have a question.

BTW. My accounting statements may be correct but the tables were done on the fly.

[June7]

Yes, GP (now Dynamics GP) is modular, gets really, really expensive. I am sure the 'unpaid invoices' reporting is part of the 30-,60-,90- day balance aging algorithm. Way too complicated for me!

[Rainlover]

One of my Jobs was when we appointed a new State Manager, I flew there and taught them about our accounting procedures plus other stuff.

Sometimes it was like talking to the rear end of a ............ But they paid me the big bucks so I was happy.

This company is half owned by Siemens. So we had to do everything accounting wise better than right.

[orange]

recyan,


I found this at the link I gave earlier. I know that Zuora is a package/product, but they are a supplier of accounting software so some of the concepts must be general.


Here's the take on Overpayment -- closest I could find to Advanced Payment

Credit Balance: The credit balance feature allows you to accept overpayments and apply an overpayment (treated as a "credit") towards a future invoice just as you would apply a payment.

Here's the Zuora link http://knowledgecenter.zuora.com/C_Z...nt_Data_Source

Just to be clear, I found the Zuora link while googling for Advanced Payments and Invoices data model. I have no affiliation with Zuora, and hadn't heard of it until researching this thread.

I'm sure there are some accountants on the forum.

[recyan]

Hi Orange,
Thanks for the link,


To be frank, I am at crossroads here.
Was looking at things from a simple application design point of view, to describe it :
__________________________________________________ _______________


The purpose of the application :
1) Record Invoices Generated against Customers.
2) Record Payments received from Customers.
3) The Payments may be in the form of

• a) Advance Payments which may later be adjusted against Invoices as & when generated.
• b) Single Payment against Single Invoice.
• c) Single Payment against Partial Invoice Amount.
• d) Single Payment against Multiple Invoices, perhaps with One of the Invoice being partially paid.

4) Ability to generate statement of Invoices & Payments received against them.
5) Ability to generate statement of Pending Invoices & Payments to be received against them.
6) Ability to generate Customers Payments Balance status - Positive or Negative.


__________________________________________________ _______________


Did not think from Accounts point of view, as this application, was just something that popped in to my head.
It was the some what, though not exactly, circular thing, that I could see in the relationships between the tables, which held my attention.
Would be grateful, if someone could explain, how the design would face problems, if implemented with data ( think, it is the queries which would perhaps face problems ). Re-posting the image once again for reference.




It is more of an academic interest, as I doubt whether I will be proceeding beyond the design phase with this idea, as I already have a simple automated Billing application, albeit, without the intricacies & it suffices my requirement.

Meanwhile, will try to see if i can bypass the accounting intricacies & come up with something.

Edit : Rainlover - Pls ignore the table names. Did not get down to changing it.
Thanks

[Rainlover]

recayn

I assume you were taught to use some special tool to design the tables. I have seen this many times before. There is a tool that is much better and shows more detail. Especially it shows the Primary Key and which Foreign Keys it is linked to.

Naturally I am referring to using Access. It does a better job and produces a better result. However the best thing is that once you get it right you don't have to do it all over again.

[Rainlover]

recayn

I just had a thought.

What is wrong with the companies accounting package. What ever package they are using you would be able to get all the reports as you mentioned directly from that package.

[orange]

recyan,

Here's a model from databaseanswers that deals with Customers, Invoices etc. It may be useful to you.
http://www.databaseanswers.org/data_...ices/index.htm

There is an earlier version (less detail) here
His FinancialTransaction deals with Payments, Refunds, ...

As for potential issues with your current model, all I can say is make up some test cases and try them against the model.

Rain asks a good question -- Does current accounting system/package give info you need, or is it possible to retrieve info from it in some manner.

What tool ( or is it Sql server) did you use to produce the drawing?

[recyan]

Sorry for the delayed response. Was out of office & did not have access to the net.

recayn

What is wrong with the companies accounting package. What ever package they are using you would be able to get all the reports as you mentioned directly from that package.

Our accounting is outsourced. We only generate the Invoices & keep records of the receipts. This part is automated at our end & it works fine with our limited requirements. I was just thinking about the complications that would arise, in case, we had different possibilities of receipts ( highly unlikely).

recayn

I assume you were taught to use some special tool to design the tables. I have seen this many times before. There is a tool that is much better and shows more detail. Especially it shows the Primary Key and which Foreign Keys it is linked to.

Naturally I am referring to using Access. It does a better job and produces a better result. However the best thing is that once you get it right you don't have to do it all over again.

Am self-taught. Request your patience.
I agree with you totally about the tool provided by access.

recyan,

Here's a model from databaseanswers that deals with Customers, Invoices etc. It may be useful to you.
http://www.databaseanswers.org/data_...ices/index.htm

There is an earlier version (less detail) here
His FinancialTransaction deals with Payments, Refunds, ...

As for potential issues with your current model, all I can say is make up some test cases and try them against the model.

What tool ( or is it Sql server) did you use to produce the drawing?

Regarding the tool used in designing the diagram,
Actually, I was working with Argo-UML at home ( learning to use it ) for this design, when my friend saw me struggling with it & told me he would give me the design in a jiffy the next day.
After your queries, I asked him. Orange, you are absolutely right, he has used SQL to produce the design.


Am still waiting for that Eureka moment.
Don't know, if it is something similar to Batch Numbers Inventory management.

Thanks

[Dal Jeanis]

I agree with Rain's estimation that "Allocation" is a better term for the records that relate part or all of an invoice to part or all of a payment.

I suspect that design is not inherently "circular", albeit displayed upside down. It properly represents a messy reality that will require careful design and programming. Still looking for a better mental map of available strategies, though.

Invoices and payments are permissively related entities. An invoice may be completely unrelated to any payment, and vice versa. As such, you should expect that the answer to a query must vary based upon how you phrased the query. A query about invoices will not encompass any payments not yet related to an invoice, and vice versa. It is unavoidable due to the entities represented by the database, not due to an error in the form of the database.

I suspect that "perfect" db design for this app just isn't possible, so any accounting rules would have to be enforced by the application processes. For instance, every customer might automatically receive a "NULL Invoice (=Advance Payment) " and "NULL Payment (=Unpaid Invoice)" for default allocation. Whether that should be a real record in the Invoice or Payment tables, or a NULL in the relevant key on the Allocation table, is a matter of study. And thinking about how to account for discounts is giving me a tummy ache