"cycles or multiple cascade paths" in Design

[recyan]

Sorry for the delayed response.

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.
............
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. .......

Thanks for the insights Dal.
One of the reason, I believe for the complication is,
The InvoiceID & PaymentReceiptID both have Client_ID defining them & they in turn are foreign keys in tblPaymentsReceiptsInvoices.
But then, as you say

It properly represents a messy reality that will require careful design and programming.

Still looking for a better mental map of available strategies, though.

Same here, I have put the design on a slow boil, as being a self-study project, I don't have the pressure of any TAT.
Do let me know, if you come across some ideas.

Thanks

[Rainlover]

Naming Convention. In order to distingush between Primary Keys and Foreign Keys I have used PK and FK rather than the normal ID for both. Naturally you should use the convention you are happy with.

tblAllocations
AllocationPK (Autonumber)
InvoiceFK (Number) Joined to tblInvoices.InvoicePK
Value (Currency)
DateReceived()

Depending on your requirements you may need to create another tblAllocationDetails which would be similar to tblInvoiceDetails.

tblInvoices
InvoicePK (AutoNumber) Joined to tblAllocation.InvoiceFK
ClientFK (Number) Joined to tblInvoiceDetails.InvoiceDetailPK
InvouceNumber(Text or Number but most likely Text for Alpha Numberic e.g. E45630)
InvoiceDate

tblInvoiceDetails
InvoiceDetailPK (AutoNumber)
InvoiceFK(Number) Joined to tblInvoices.InvoicePK
Item (Text)
Quantity(Number)
ItemValue
Other Item Details etc

tblClients
ClientPK(AutoNumber) Joined to tblInvoices.ClientlFK
ClientName
ClientContact
ClientPhone
ClientTerms.

There may be more than one contact or phone so it would be appropiate to create another Table tblClientDetails.

TblInvoiceReceiptPK (AutoNumber)
ReceiptPK (AutoNumber)
ReceiptNumber (Textor Number but most likely Text for Alpha Numberic e.g. E45630)
ClientFK (Number)
ReceiptDate(Date)
ReceiptValue

Monies received from are client are always applied to an Invoice. Advanced Payments are really Deposits. These receipts are applied to a Particular invoice as a part payment. Discounts are usually applied to a CreditNote.

One should always apply receipts as per the Clients Instructions. If in doubt leave it in the Invoice Receipt Table until you contact the client for proper instructions.
I would think that the above design will require fine tuning. In fact I am sure of it. I suggest that you create theTables in Access, then set up your Relationships. Post a screen dump of the Relationships Window for further discussion.

[Dal Jeanis]

And remember, it has to make sense to you and your business -- not some textbook.

Words to live by.

[recyan]

Rain - I have left out the tblInvoicesDetails, to keep things simpler & have included the Value in the tblInvoices itself.
Have also left out a few fields from some of the tables for simplicity.
Do let me know, if they are important & need to be included.



Edit : Should the table tblInvoiceReceipts be tblReceipts ?

Thanks

[Rainlover]

Rain - I have left out the tblInvoicesDetails, to keep things simpler & have included the Value in the tblInvoices itself.

If you think this through you should be able to answer this question yourself. The question you ask yourself is what is this table for.
The Answer is so that you can have several items under the one invoice.
Like 4 Cats, 5 Dogs, A Cow and 16 horses. This would give you 4 lines or records in tblInvoiceDetails.

If on the other hand each invoice were for one item every single time then you would not need the tblInvoiceDetails table. One Item could be 5 Dogs @ 17.55 each. However, if each dog has a different value then you need 5 records. Therefore, the answer is up to you. In any case, you would need fields something like Item, Item Description, Value and maybe other things like color, which has not been mentioned.

Have also left out a few fields from some of the tables for simplicity.

That is fine for now, just realise you may need to add back some fields later. Not worth losing sleep over at the moment.

Should the table tblInvoiceReceipts be tblReceipts?

You are correct.

From here, I would suggest you alter your tables to how you want them and post your revision here together with your next question.

I have never written a Database like this before but I do have a sound knowledge of accounting in this area. What I need to do is to work out how we apply the Money received against the proper invoice. I will do some Goggling to see what I come up with. However, don’t just wait for me. Have a go at doing your own research.

[orange]

As noted previously, I am not an accountant. I looked up the definition for an "allocation" in finance terms and found this:

allocate1. To spread systematically a single monetary amount over a number of time periods, usually years. For example, depreciation allocates the cost of a capital asset over its useful life.
2. To distribute cost or revenue throughout a number of operations or products. For example, a business must decide how to allocate the costs of running its headquarters over all its operations to determine the profitability of each of those operations.

source: http://financial-dictionary.thefreed...com/allocation

Just for clarity and to keep us all on the same wave length - is this what Rain means or should this be adjusted to accommodate his intent.

recyan, can you make a few statements that outline a few processes that occur in your business.

eg. Client Orders x Widgets ($20) and y gadgets($40). We make up an Order and these OrderItems..... We then Create an Invoice.$(60). Client makes a payment ($40); and we apply that $40 against....then Client makes another payment of $40..
and since his outstanding Balance is $20, we apply $20 against his latest Invoice (giving us $0 outstanding), but Credit his Account with $20 (since he overpaid????)
Just need to get a reasonable scenario into words that we all can agree to, and work through a model, until it satisfies your needs/requirements.(and I realize some of these steps may be outside your scope at the moment , but they do fit a bigger picture).

Just trying to help get a working model.

[Rainlover]

The word allocation as it is used here is to allocate a payment from a Client to one or more invoices of the client's choice. It could be that the oldest invoice is in dispute so to apply a payment to that invoice would be incorrect.

If one simply allocated to the oldest invoice then how would the credit manager, who's job it is to chase up payments for overdue invoices know which invoice/s he is chasing. So it is important to allocate to the correct invoice. Could you imagine attempting to prove in a court of law what was not paid and what was. I think the case would be thrown out before it got to court.

[orange]

Rain, I agree. I'm playing devil's advocate a bit just to make sure we're all on side with the terminology. I'd like to see a reusable data model evolve, since there doesn't seem to be one readily available for free that includes the concept of "prepayment". In fact, the data models for Customer, order, invoice don't always have partial payment, prepayment/credit etc.

Based on your response, our "allocation" refers to the application/assignment of a Payment to an Invoice(s) identified by the Client. ( I take it the client could assign partial payments to multiple (>1) invoice)

My scenario was to be the plain vanilla just to overview the processes and to have recyan expand as needed.
I think the points you make should be part of the scenario (the custom or not common occurrences that must be accommodated in the "business under investigation".

So we do need a test scenario in which a Payment is in dispute. Perhaps the Payment from the Client did not identify the Invoice(s) to which the Payment should apply.
It also seems we need to have a means to identify an Overdue Invoice(s).

[Rainlover]

Recyan

Is this getting too complicated. Or to put it another way are you understanding where we are now and where we need to get to.

One thing I can say is that if this was my database I would be doing it the way I have described. I also know that it could become even more involved down the track, especially when people start asking for reports.

I don't know how much you get paid but let us say for the sake of the exercise you get $100.00 per hour. The cost of purchasing say "MYOB" at a guess would be say 5 to 10 hours of you time.

I can see 3 - 400 hours work in this just for the basics.

Food for thought.

BTW I have found no Templates in my searches, which could mean there are very few people who have the right experience.

[recyan]

Thanks to all of you for the replies.
Give me some time to digest it & reply.

Thanks

[recyan]

If you think this through you should be able to answer this question yourself. The question you ask yourself is what is this table for.
The Answer is so that you can have several items under the one invoice.

Rain - I am deliberately ignoring the InvoiceDetails table at present & for all purpose, considering, One Invoice has only One Item in it. I do understand, what you are pointing out, but presently, we can safely ignore it & proceed.

recyan, can you make a few statements that outline a few processes that occur in your business.

eg. Client Orders x Widgets ($20) and y gadgets($40). We make up an Order and these OrderItems..... We then Create an Invoice.$(60). Client makes a payment ($40); and we apply that $40 against....then Client makes another payment of $40..
and since his outstanding Balance is $20, we apply $20 against his latest Invoice (giving us $0 outstanding), but Credit his Account with $20 (since he overpaid????)
Just need to get a reasonable scenario into words that we all can agree to, and work through a model, until it satisfies your needs/requirements.(and I realize some of these steps may be outside your scope at the moment , but they do fit a bigger picture).

Orange - Exactly as you put it, except for the OrderItems which we can ignore & take only Order with its Value.
Going by above example, Order1 - Value $60.

The word allocation as it is used here is to allocate a payment from a Client to one or more invoices of the client's choice. It could be that the oldest invoice is in dispute so to apply a payment to that invoice would be incorrect.

If one simply allocated to the oldest invoice then how would the credit manager, who's job it is to chase up payments for overdue invoices know which invoice/s he is chasing. So it is important to allocate to the correct invoice. Could you imagine attempting to prove in a court of law what was not paid and what was. I think the case would be thrown out before it got to court.

Rain - In total agreement.

Rain, ................

Based on your response, our "allocation" refers to the application/assignment of a Payment to an Invoice(s) identified by the Client. ( I take it the client could assign partial payments to multiple (>1) invoice)

My scenario was to be the plain vanilla just to overview the processes and to have recyan expand as needed.
I think the points you make should be part of the scenario (the custom or not common occurrences that must be accommodated in the "business under investigation".

So we do need a test scenario in which a Payment is in dispute. Perhaps the Payment from the Client did not identify the Invoice(s) to which the Payment should apply.
It also seems we need to have a means to identify an Overdue Invoice(s).

Orange - Yes, to everything

Recyan
Is this getting too complicated. Or to put it another way are you understanding where we are now and where we need to get to.

Not at all, Rain. Am enjoying the learning process, Thanks to all of you.

Recyan
One thing I can say is that if this was my database I would be doing it the way I have described. I also know that it could become even more involved down the track, especially when people start asking for reports.

If I am not wrong, we are talking about the Accounting way. Have kept my mind open to it also & trying to read up a bit on basic accounting, though, must admit, am finding it a bit tricky. Also was thinking about Dal's suggestion on handling it at application level and about the implications of doing so.

Recyan
I don't know how much you get paid but let us say for the sake of the exercise you get $100.00 per hour. The cost of purchasing say "MYOB" at a guess would be say 5 to 10 hours of you time.

I can see 3 - 400 hours work in this just for the basics.

Food for thought.

Rain. Never used to analyse things that way. Makes lot of sense. In fact, what you suggest, should be the way to go, in an actual scenario. Thanks for that. But in this case, It's a self-study project, which just came up in my head, due to the intricacies involved.

Recyan

BTW I have found no Templates in my searches, which could mean there are very few people who have the right experience.

Same here, Rain, though, my search would have been less thorough. I thought, the scenario of handling advance payments should exist, even if not common.

Guess, will have to plug away.

Thanks

[Rainlover]

Having read your last post, why don't you just go ahead a write the queries forms etc.

Don't worry about pretty forms just do what you can.

Naturally you will come into problems, so lets find a solution for each problem as we go.

I am very happy to help with this one given the time. I would like to see a finished project.

Good Luck.

[recyan]

Having read your last post, why don't you just go ahead a write the queries forms etc.
Don't worry about pretty forms just do what you can.
Naturally you will come into problems, so lets find a solution for each problem as we go.

Actually, was planning to leave this at the Design stage itself, after satisfying myself. But, as you suggest, I think, will have to proceed to the Forms, Queries, Reports, etc stage. Will try doing that & keep you posted.

I am very happy to help with this one given the time. I would like to see a finished project.

Time is not a problem, Rain. Like I told earlier, it's a self-study project & there is no TAT involved.

While typing this reply, a weird thing came to my mind :

tblClients
ClientIDPK - PK
ClientName

tblInvoicesReceipts
InvoiceReceiptIDPK - PK - AutoNumber
InvoiceReceiptNo - Text
Type - Values - Invoice / Receipt
ClientID_FK
Date
Amount
AllocatedInvoiceReceiptIDPK - FK from the same table. The ReceiptID is allocated only to Invoices. The Receipts will have Null or 0 Value.

Haven't shot it full of holes so far, as it has just come up. If you guys find it full of bugs, let me know asap.

Thanks

[Rainlover]

Watch out for Reserved Words. Do not use them. Use MyDate not Date.

These words have special meaning and are predefined in the language’s formal specifications.

Examples. Date, Now, Name plus many others. You can do a Google for a list.

Naming of fields. There are mainly conventions rather than rules. Like No Spaces and No Special Characters like #$%^&*( etc)

There is a link in my signature that will take you to my Sky Drive. There is a paper on Naming Conventions. You may find it interesting.

[Rainlover]

Recyan,

I realise you are not in a rush but I had some time. Things do get a little different when you start to apply them. So this pic is what I have so far, but it is not 100% correct.