"cycles or multiple cascade paths" in Design

[recyan]

Is this " cycles or multiple cascade paths", something to be avoided at all cost in Database design ?


Does it reflect a flaw in the design or Can one ignore it occasionally, based on it being a necessity ?

Thanks

[June7]

Here is a discussion http://social.msdn.microsoft.com/For...c-3e456f4f80ac

It refers to a MSKB article http://support.microsoft.com/kb/321843

I haven't encountered that term before. Would it be synonymous with 'circular reference'? Review http://www.codeproject.com/Articles/...atabase-Design

[recyan]

Hi June,
The 3rd link of codeproject, which you have provided is the scenario, that I am speaking about.
Not sure, if it is synonymous with 'circular reference' (assuming, we are talking about Excel).

Am attaching a scenario for checking if there is some flaw in the design :
1) Clients make Payments.
2) The Payment made may be against Invoice / s or may be Advance Payment, which later has to be allocated against Invoices, raised at later Date / s.

Thanks

Hi June : Somehow managed to attach a 2nd attachment "cheyanne.zip" by mistake & am unable to remove it, If possible, can you delete it.
Ignore above request. Managed to delete it.

Thanks

[orange]

recyan,

Not sure I'm following your underlying question, so this may be way off topic.
Payment is made by a Client
Payment may be for 0,1 or many Invoices (I highlight 0 here because of your Advanced payment)
.... but normally a payment would include an Invoice Number
So a Payment by a Client with no invoice number may be considered " a credit to this Client's account"

Just some thoughts to consider.

Good luck.

[June7]

That diagram looks like the scenario that is advised to avoid. It is 'circular'

[recyan]

recyan,

Not sure I'm following your underlying question, so this may be way off topic.
Payment is made by a Client
Payment may be for 0,1 or many Invoices (I highlight 0 here because of your Advanced payment)
.... but normally a payment would include an Invoice Number
So a Payment by a Client with no invoice number may be considered " a credit to this Client's account"

Just some thoughts to consider.

Good luck.

Hi orange,
It is exactly the way you describe it. Do not know if you are hinting at something. But all the same, your words got me re-thinking again.

Thanks

That diagram looks like the scenario that is advised to avoid. It is 'circular'

You are right June. I just can't think about, how to avoid it.
At the same time, I do not know, whether in certain situations, these kind of designs are acceptable.

Thanks

[June7]

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?

[recyan]

I do not know anything about accounting or accounting db's.
Often have these weird things entering my mind & then start designing dummy projects for analysis.
As to your question, As to why Payments have to be associated with Invoices,
the way I thought was,
there could be a possibility, that there is dispute about a certain Invoice/s & the Client insists that the Invoice/s should be kept pending till resolution & Payments should be made against subsequent Invoice/s.
Keeping in mind above, I designed the db in the manner I have shown in my earlier post.

Thanks

[orange]

recyan,

No, I wasn't hinting to anything. I was just making clear WHAT I got from your post, and played it back in my own words. I was just trying to make sure I understood. I mention the Credit, because my feeling is you make a payment against an Invoice/Bill.
But I am not an accountant.

If you look at various data models, you see Customers, Orders, OrderItems, Products, Invoices, Deliveries, PartialDeliveries, PartialPayments, BackOrders, Accounts.....
"AdvancedPayment" was new to me (but many things are), and in my terms that was a Credit/Surplus. It stood out because it was not associated with an Invoice or Bill.
As I said I am not an accountant, haven't dealt a whole lot with Finance, so my thinking may just be a lack of experience.

If the AdvancedPayment makes sense in your business/environment, and you have a clear definition of it, then go for it.

I'd like to see the broader data model, the picture from earlier post seemed to be taken from some other context.

You're aware of the data models at http://www.databaseanswers.org/data_models/index.htm Many of these have parts of what you are dealing with, but there doesn't seem to be one model that encompasses everything.

You may find some helpful concepts here
http://knowledgecenter.zuora.com/C_Z...nt_Data_Source

Good luck,
orange

[recyan]

recyan,
Payment is made by a Client
Payment may be for 0,1 or many Invoices (I highlight 0 here because of your Advanced payment)

Sorry Orange, missed out a point in my earlier reply against this.
Payment can also be for Partial Amount of an Invoice ( i.e. Part Payment ).

recyan,
I'd like to see the broader data model, the picture from earlier post seemed to be taken from some other context.

No orange, there is nothing beyond what I had posted earlier.
It might appear in-complete, as I created tables with bare minimum fields which were critical to the analysis of the design.

Currently, would like to understand the implications of the circular relationships occurring.

Thanks

[orange]

Circular relationships from my understanding, and reading, imply something isn't quite right with the model or understanding of the entities or relationships. Here is a link that may help focus on the issue.
http://www.tdan.com/view-articles/9169

Do you have a list of business facts? Do you have some test cases/data?
We used to get people to identify the facts; then build a draft model; then get people back to play "stump the model".
People would throw out business facts/scenarios and , as a group, we'd work through the model. If the model accommodated the fact, all was good. However, when the model failed to handle a fact 2 questions arose:
Is it the model? or Is it the test Data (basically do we have the facts right). Every test case had to succeed, else the model had to be adjusted accordingly-- once we knew the facts were correct.
This little game was attacking the model --Not the modeller and that's important. Surprising how many facts/definitions were cloudy at first, and everyone learned about the details as we progressed. The objective was to get the model designed before getting in to programming etc.

[recyan]

Hi Orange,

A bit tied up with somethings.
Will revert asap.

The second part of your reply - "business facts / Test Cases" - are something that I will have to look in to, as I have not got down to it. Everything is in my head.
Bcos in my case, I am the Client, I am the Designer, I am the developer, etc. Just keep on fiddling with / trying to automate thoughts as & when they come to my head.

Thanks

[recyan]

Here is a link that may help focus on the issue.
http://www.tdan.com/view-articles/9169

Thanks for the link.
After going thro the link, came to the conclusion, that what I have, is not circular relationship. Rather it appears somewhat like the triangular shape described ( Might be a new model - the Rhombus Model ). Am currently analyzing my design model to see if I can get over it.
As to the Business Facts, I am not able to think anything beyond what I have already posted.
If you want me to summarize the thoughts ( Business Facts / Test Cases ) in some particular manner, would request, if possible, to provide some link, as to how it is done.

Thanks

Edit : Posting earlier image for reference :




Was wondering, what if I include fields PaymentReceipt_ID & PaymentReceiptAmountAllocated in tblInvoices & get rid of tblPaymentsReceiptsInvoices.

Let me know, if u see something fishy. Will it lead to a triangle from the rhombus???

Edit :
Not possible : - Will lead to InvoiceID primary key violation, as an Invoice can have multiple partial payments against it.

Thanks

[orange]

Here's a link to the facts
http://www.databaseanswers.org/data_...ries/facts.htm
that relate to this data model
http://www.databaseanswers.org/data_...ries/index.htm

There are other models/facts at the site. I selected this one at random. Some samples are much better than others at the site.
My concern with the circular /triangular/rhombus is that there may be some aspect/attribute that is masking another entity, or a definition that is including something that it shouldn't..
Systematically -- build a draft model; define the facts and build some test data (good and bad); then test the model. If you end up with a "can't get there from here" situation, then revise the model. If it all works, then go for it. But as you know, get the model designed to support the business before getting into detailed code only to find you need to build some "workarounds".

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

[recyan]

My concern with the circular /triangular/rhombus is that there may be some aspect/attribute that is masking another entity, or a definition that is including something that it shouldn't..

This is exactly what I am worried about.
Thanks for the links. Had gone to those models quite a few times. Never realized, they had those Descriptions behind them.
Will try to put my head down to it.

Thanks