"cycles or multiple cascade paths" in Design

[Dal Jeanis]

I would think that putting AllocatedInvoiceReceiptIDPK on this table would fail the normalization rules, especially if you are still trying to allow an invoice to match multiple receipts and vice versa.

[Rainlover]

I would think that putting AllocatedInvoiceReceiptIDPK on this table would fail the normalization rules, especially if you are still trying to allow an invoice to match multiple receipts and vice versa.

There is no "AllocatedInvoiceReceiptIDPK " anymore.

I can attach the Database should you wish to play with it.

Yes One Payment could cover many Invoices and Many Payments could be allocated to one invoice.

[Dal Jeanis]

That was just a comment on his "weird thing" in post 43 about noon yesterday. Since you had responded with naming conventions and a new design, but no specific other feedback, I figured I'd point out the biggest hole in his "weird thing" boat.

[recyan]

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

Will take care, Rain.

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.



Attachment 12687

Rain, You have already indicated, above is not 100% correct. All the same, a few doubts :

tblReceiptDetails
1) If I am not wrong, we can get rid of ClientFK due to the presence of InvoiceFK.
2) Was not able to find the co-relation of Receipt / s & Invoice / s in the table.

I would think that putting AllocatedInvoiceReceiptIDPK on this table would fail the normalization rules, especially if you are still trying to allow an invoice to match multiple receipts and vice versa.

Dal - Did not look at things from Normalization point of view. Guess that is the first thing, I should have done, at least till 3NF.

_______

Thanks to all of you for being there and for keeping me from giving up, out of frustration.

Thanks

[Rainlover]

Rain, You have already indicated, above is not 100% correct. All the same, a few doubts :

tblReceiptDetails
1) If I am not wrong, we can get rid of ClientFK due to the presence of InvoiceFK.
2) Was not able to find the co-relation of Receipt / s & Invoice / s in the table..

Thanks

1) You may be correct, however I would like to leave it there until I find the proper design. I can always delete what's wrong later.
2) You can't "find the co-relation of Receipt / s & Invoice / s in the table." The problem is that neither can I. My mind is not working as good as it should be.

I posted the Relationship diagram to let you guys know where I am and to get some feed back, which I am getting.

My next aim is to be able to create a Main Form that displays ONE ONLY Client together with the total value received against that Client but not yet allocated to individual Invoices.

In the Sub Form I want to display all invoices from that Client. It should only show closed invoices. A closed Invoice is one that is fully paid.

This step should be simple but alas it is not turning out that way.


Feedback from anyone would be welcome.

[orange]

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.



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

recyan,
Since you described this as a self study with no real time pressure, you may find this info from Oracle of interest. It is somewhat specific to their approach and software, but, on the other hand, it describes certain scenarios and what is involved. There are a lot of concepts multiple and partial payments; prepayment; multiple payment types per order... I think it's worth a good glance since it's another source; they are a viable company and this describes things based on a commercially available package. May not all be directly useful, but it's a learning project you are undertaking.

I have not been able to find a free data model that shows prepayments, partial payments, invoices, orders etc.
If you have an evolving model, and the business facts that go with that model, then please post. I'm sure you'll get comments/critique.

Similar to an architect who can draw an "artist's concept" of a new building, you can build a data model (tables and relationships) without building an application.

Good luck.

[recyan]

1)
I posted the Relationship diagram to let you guys know where I am and to get some feed back, which I am getting.

Realized that. Will keep you posted if something comes up.

Thanks

recyan,
Since you described this as a self study with no real time pressure, you may find this info from Oracle of interest. It is somewhat specific to their approach and software, but, on the other hand, it describes certain scenarios and what is involved. There are a lot of concepts multiple and partial payments; prepayment; multiple payment types per order... I think it's worth a good glance since it's another source; they are a viable company and this describes things based on a commercially available package. May not all be directly useful, but it's a learning project you are undertaking.

Thanks Orange, Will take a look asap.

recyan,
If you have an evolving model, and the business facts that go with that model, then please post. I'm sure you'll get comments/critique.

Currently, no. Everything is in a state of flux in my mind, what, with a few ideas coming to my mind & then crashing with some bug or the other.
The only model I have currently, is the one which I posted at the start of this thread.

recyan,
Similar to an architect who can draw an "artist's concept" of a new building, you can build a data model (tables and relationships) without building an application.

This is exactly, what I would love to do.

Thanks