How do you handle data relationships where no clear relationship exists

[Kryst51]

Once again I am posting about the design of my nonconformance database. Please find attached my current relationship layout.

Nonconformances occur for all types of reasons. Most of them involve material, some related to a customer, some not, some related to an outside vendor, or some that has been produced by us. Credits and Debits of all shapes and sizes need to be able to be done. Some that are for specific tags on an NCR, while other tags will not be debited. Same thing with the credit, the credit can occur for a specific tag on an invoice, or if there is no material returned a fixed amount may need to be done, etc. Hopefully you can see from this how diverse the scenarios can be.

I have a base idea, where I have a CreditDebitHeader table, which stores the type (Credit or Debit or Freight Claim) and a CreditDebitDetail table, which stores the details. What I am unsure of is how this should link in with the rest of the information, given the necessity for versatility. The report that will be generated needs to have the customer name, invoice number (if applicable), tag numbers (if applicable), item(which is related to the tag) plus various other information such as the NCR number, the date, Return info (if applicable) etc. Plus the credit details. If it is for a specific invoice, grouped by customer, invoice, item. If it is one lump sum for the customer, then grouped by customer only as sometimes there is more than one customer on an NCR, and no particular invoice to reference, or one lump sum and all invoices need to be referenced but not grouped by.

In my current db, I have several tables to handle this that duplicate the same structure, and hold similar information but attach to the other tables at different places. I am hoping there is a better way, but am at a loss as to how to do it. I have been drawing diagram after diagram to try to visualize what needs to happen but just can’t wrap my mind around it.

If anybody can provide a direction to think about I would be grateful. I realize that this is long, and may not be the best way to explain the problem, so please ask me questions if you don’t understand what I am asking.


Edit: One thing I just realized, that I will be changing in my relationships, is that there may not be material involved, but there may still be a customer, or vendor, plus there corresponding invoices or pos given various situations. So this might change the whole relationship screen.

Edit 2: I just realized that I can leave it as it is, as the tag header doesn't need a tag necessarily but can still reference a PO and/or invoice, etc. so I think that will be fine.

 

[Banana]

Unforunately and probably on my account of being unfamiliar with what is going on here, I'm not sure what is it you're looking for.

What I could at least offer is this:

If you have two thing that are similar but differ in one respect, they could belong in same table with a extra column to represent the attribute where they can differ. For that reason, I would just have a tblTransactions which stores both credit and debit with an extra column to a lookup table to identify what kind of transaction this is, a credit or debit (could there be other kinds of transaction? I can see some business wanting to mark some transactions specially so lookup table may be a better choice for expandability though one could get away with just a checkbox but that's mighty constraining).

On the other hand, when you have something that may apply or not for this thing, and/or you are uncertain about the number of times it may apply to the same thing, it usually means a separate table. So, it is conceivable that an Invoice table could have a separate table to store customers and another table to store vendors to enable creating an invoice where there are multiple vendors selling to multiple customers on the invoice. Odd business rule, to be sure, but if that's the requirement then that is the correct way to approach it.

Did it help?

 

[Kryst51]

Unforunately and probably on my account of being unfamiliar with what is going on here, I'm not sure what is it you're looking for.

What I could at least offer is this:

If you have two thing that are similar but differ in one respect, they could belong in same table with a extra column to represent the attribute where they can differ. For that reason, I would just have a tblTransactions which stores both credit and debit with an extra column to a lookup table to identify what kind of transaction this is, a credit or debit (could there be other kinds of transaction? I can see some business wanting to mark some transactions specially so lookup table may be a better choice for expandability though one could get away with just a checkbox but that's mighty constraining).

On the other hand, when you have something that may apply or not for this thing, and/or you are uncertain about the number of times it may apply to the same thing, it usually means a separate table. So, it is conceivable that an Invoice table could have a separate table to store customers and another table to store vendors to enable creating an invoice where there are multiple vendors selling to multiple customers on the invoice. Odd business rule, to be sure, but if that's the requirement then that is the correct way to approach it.

Did it help?

Well, I have accounted for the type (credit vs debit). But what I can't account for is (as an example) a credit report that will be generated will ALWAYS be related to a customer and NCR, However, it can be more detailed then that. it may or may not be related to return material, may or may not have specific tags associated with it. The tags involved may or may not involve an invoice or the credit may invove an invoice, but there will be no tags.

So at any given time it looks something like this:

Credit, NCR, Customer

or

Credit, NCR, Customer, Invoice(s)

or

Credit, NCR, Customer, Invoices(s), Item

or

Credit, NCR, Customer, Invoice(s), Item, Tag(s)

each scenario getting more detailed or less detailed depending on the scenario.

Edit: There may be Invoices, tags, customers, items on the same NCR, etc that do not belong on the credit report.

 

[Kryst51]

Maybe there should be an in-between table (between the header and detail) it could store invoice(s) and/or tag(s)...... that way you could group by invoice or by item as each tag must be associated with an item.

 

[CraigDolphin]

Like Banana, I'm a bit lost by the original description. But if your report always has Credit, NCR, and Customer then the information pertaining to these entities ought to be in the main report itself. And information pertaining to the other entities (Invoice, Item, Tag) should be contained in subreports within your main report. If there is pertinent data available, the subreports will be present. If not, then they will be absent. Exactly how the data that is associated with these entities is nested within the your main report is probably something only you can figure out. For example, tags might be a subreport within the 'Item' subreport.

The report/subreports are linked by a key field in the same way as forms/subforms are. Apologies if this post misses the mark.

 

[Kryst51]

I want to reply more clearly, but the time has come to go home... I will try to explain better tomorrow. Thanks guys for your help thus far.

Edit: I thought of a solution while I was driving home from work today, that I hope to test tomorrow. And though I know the problem wasn't clear, maybe the solution will help clear up the problem. I'll post back tomorrow.

 

[The_Doc_Man]

Sometimes the solution is to step away from the keyboard. Put the mouse down and nobody will get hurt...

OK, non-standard relationships are easy. It's hard because we tend to overthink them. I'm going to wander along the garden path for a while here. See if any of this helps to reset your thinking.

First, set up your tables without concern for ANY relationships at all. Once you are done with that, look for the commonalities. If there are none, you've beat yourself up about the impossible. Stop doing that.

However, if there are at least a few common themes that might be the basis of relating things, understand that you can relate the common parts pretty easily. It is going to be the non-common parts that eat your socks for you. Don't try to relate them right away.

I don't understand the financial environment but I do understand that you will have what might be called a sparse set of relationships. Isolate everything down to the basics. Maybe make a one-to-one relationship with an extension table for the extended attributes and ignore the extras when trying to do correlations on the common parts. You attack them in a second pass, because after all, program development is not an atomic process. It occurs in stages. So divide and conquer. Worked for Julius Caesar, it can work for you.

Maybe you should consider junction tables as a way to express relationships in a non-standard way, perhaps. Or consider UNION queries as a way to bring disparate tables together in their common or similar structures. Remember that in the query grid, the upper half of the query pane is a mini-relationships diagram that allows you to CANCEL existing high-level relationships or CREATE one-query relationships that apply between two recordsets. (Yes, you can create relationships between queries this way. It works.)

I have always found that when I face an insoluble problem, I can usually make progress even if I can't reach the solution. Sometimes I just change the problem. The idea of the one-to-one table for "additional details" that are outside of the commonalities is one way to change the problem. The idea of UNION queries is another. The idea of local relationships is a third way and can be very useful even when the relationship in question is not natural. (But then again, I've always been a fan of doing unnatural things... )

 

[Kryst51]

Sometimes the solution is to step away from the keyboard. Put the mouse down and nobody will get hurt...

OK, non-standard relationships are easy. It's hard because we tend to overthink them. I'm going to wander along the garden path for a while here. See if any of this helps to reset your thinking.

First, set up your tables without concern for ANY relationships at all. Once you are done with that, look for the commonalities. If there are none, you've beat yourself up about the impossible. Stop doing that.

However, if there are at least a few common themes that might be the basis of relating things, understand that you can relate the common parts pretty easily. It is going to be the non-common parts that eat your socks for you. Don't try to relate them right away.

I don't understand the financial environment but I do understand that you will have what might be called a sparse set of relationships. Isolate everything down to the basics. Maybe make a one-to-one relationship with an extension table for the extended attributes and ignore the extras when trying to do correlations on the common parts. You attack them in a second pass, because after all, program development is not an atomic process. It occurs in stages. So divide and conquer. Worked for Julius Caesar, it can work for you.

Maybe you should consider junction tables as a way to express relationships in a non-standard way, perhaps. Or consider UNION queries as a way to bring disparate tables together in their common or similar structures. Remember that in the query grid, the upper half of the query pane is a mini-relationships diagram that allows you to CANCEL existing high-level relationships or CREATE one-query relationships that apply between two recordsets. (Yes, you can create relationships between queries this way. It works.)

I have always found that when I face an insoluble problem, I can usually make progress even if I can't reach the solution. Sometimes I just change the problem. The idea of the one-to-one table for "additional details" that are outside of the commonalities is one way to change the problem. The idea of UNION queries is another. The idea of local relationships is a third way and can be very useful even when the relationship in question is not natural. (But then again, I've always been a fan of doing unnatural things... )

This is a lot to digest, and after reading it a few times, I am uncertain that I understand it all. However it has given me new approaches to my thinking, so I am mulling over several ideas. Although, I am curious what you mean by "local relationships" I don't know that I have ever heard that term.

 

[Kryst51]

OK, Here's what I finally did.

I took out the credit header table by realizing that it is completely useless. I build the header for the report using a query, nor a table... Duh.

Second I gave my charge detail table three identifiers, one is a "Category" which identifies whether or not it is a credit or a debit, One is a "Type" which identifies which level the charge belongs (ie - invoice level, or item level, etc.), and three is a number which depending on what the other two options is the ID which ties it to the rest of the database. I also store the NCRHeaderID, as this with the beforementioned information makes it unique. None of this information is "related" in the relationship screen, but the relationships will be done through queries. There will be one query for each group of the report, which will pull together only the credit details for that report grouping. Ahhhh - Finito! Thank you everyone for getting me to think outside of the box!

 

[The_Doc_Man]

Just FYI, regarding "local relationships"

When you are on the Tables window of the database and you click the appropriate Icon, you open the Relationships window. Those relationships are available to every new query that you create. They are "global" to the database.

If you open a new query in "grid" mode, the bottom half of that screen is a grid of fields that you will see in the query's recordset. The top half of that screen is a "localized relationship" window in that you can define a relationship in that window that is NOT available to all other queries. You can also OVERRIDE an existing global relationship to remove it, reverse the arrow direction, or change the integrity rules.

The implication of a relationship in a multi-table query is that the SQL generated for this query will include " yada yada yada INNER JOIN ON yada yada yada" based on that relationship. I.e. you don't have to tell it in the query to JOIN on the XXID field of two tables if you have pre-defined the relationship. Well, the top half of that query grid is the way for you to graphically define the relationship you want to be in force for that query only.

This amazing ability even allows you to force a relationship between fields that really should NOT be related globally but maybe you are doing data mining and you are looking for "chance correlation" or something like that. It's a very powerful QUERY tool that is, IMHO, totally underappreciated.