General understanding of relationships

[Compressor]

Please see the attached two files.

I have tried to figure out what the difference in functionality would be between 1 and 2. In the latter I have just created a series of relations between the last two tables (but that structure could be used throughout the whole DB instead of hanging it together with one relation between tables as is the case for the rest of the DB in this picture).

So there can be three different situations with this table structure:
- linking all tables together via multiple relations
- linking all tables together via a single relation
- a combination of both, such as in the images.

Why would someone choose one versus the other? "Which is better in what situation?"

I've been fiddling about for a while now, but in a structure such as this it doesn't seem to make a difference. Or maybe there are differences in use/design, but on a level that I have not explored yet.

Could anyone explain this to me a bit? I have multiple books, all of which handle basic relations but I have not yet seen an explanation for this question. I think understanding the whole relationship topic is critical to be able to see other pieces of the puzzle fall into place later on. And as of yet, this is really fuzzy to me.

 

[The_Doc_Man]

I rarely download anyone's files. No offense intended, but you can never be too careful about random downloads.

Relationships exist between two tables that are logically related in some way. How does this work? (Or why?) Because the tables represent something in your problem that are also related.

We speak of one-to-many or many-to-one relationships, with and without relational integrity, with and without cascade updates or deletes. There is also that rare bird, one-to-one. And finally the many-to-many implemented via a junction table. But WHEN do you express relationships? When they make sense, of course.

Suppose we take a common business case. You write up an invoice. Many items appear on the invoice because your customer bought more than one thing during his/her visit. In the "real world" case, your invoice form has more than one place to write the names of the items purchased. In the "data model" equivalent, the invoice appears as a record in one table and the line-items are records in another table. The relationship occurs through the invoice number. Here, the invoice is the focal point, gathering together line items that belong with it.

If you have an inventory table, you would perhaps link the invoice's line items to the inventory - but you would not link the invoice itself. Knowing what was purchased is important for inventory control. But there is no natural linkage to the overall invoice number.

So in a roundabout way I am trying to explain that you develop relationships with tables where it makes sense to do so. And it will make sense to do so when you have a problem in which the elements of the problem have some natural relationship. You NEVER establish a relationship "just because." You ALWAYS examine the physical basis for your Access tables to see if the real world reflects a relationship.

Without looking at your relationship diagrams, I can tell you this: If you don't know the basis for the problem, you will never know when a relationship should exist. And you will never know by looking at an existing relationship whether it really SHOULD exist.

Having said THAT mouthful, I can also say that looking at an arbitrary diagram and asking which one is better simply tells us you don't understand the underlying problem. Or, said another way... once you have defined the tables that are the representation of your problem and have analyzed how the parts of your problem relate to each other in reality, there is no asking "which is better." There is only "accurately represents problem" or "does not accurately represent problem."

So, to your original question - why would you choose one structure over another? Because the one you choose best represents the problem you were trying to solve. ALWAYS relate back to your problem. Don't look at the tables. Look at the PROBLEM FIRST.

 

[Compressor]

Thanks for your reply.

If you right-click the file and choose Save target as... you will get an IE save file dialog. In this dialog you will see the real filename of the file you would download. If you're still not sure about that goto any windows explorer window, options, folder options, tab -> view and make sure all hidden files, file extensions and system files are always shown. That way you will never have to doubt if a file you download might be a image.jpg.vbs or another type of script instead of just a image.jpg And when you've updated windows as should be done always if your concerned about security and since we all use legal versions of windows we all can (unless you use linux ;P ) Then you won't have to worry about the "malicious code in an image file" issue since that has been patched quite a while ago. Now... when you've secured your system using a respected IS package, you really won't have much to worry about when downloading plain images. You do it all the time when visiting sites. Also ones you don't yet know to be trustworthy. Only difference is, on this forum they have chosen to not embed the images into the topic, but rather just use links to the images. Most likely to keep the layout of the site intact since people will post all kinds of resolution images into a topic, which can really scr*w up the layout. Just my 2 cents...

Back on topic:
I understand what you are saying. But in the case I have presented above, there really is only one link necessary to be able to follow the complete chain (enabled ref integrity, cascades and deletes BTW). The "which one is better" is just a metafore for the "Why would someone choose one versus the other?". By that I mean in which situation...

Indeed I do not understand the entire concept yet, that's why this question arose. For me, to be able to understand, a more practical example would have to be outlined. So that's my question really. Not a which one should I use for this DB. I've just used the thing I am working on as an example to show what I would like to understand and know more about. As I said, the books I have here really don't cover that. The functionality is there so I think it's safe to assume it serves a purpose. Or maybe.... just because you can jump of a cliff, doesn't mean you should

 

[RoyVidar]

The initial question
"So there can be three different situations with this table structure:
- linking all tables together via multiple relations
- linking all tables together via a single relation
- a combination of both, such as in the images."

There's a difference between "relationship" and "relation". In database theory and mathematics, a relation is what we would call a table. Relationship, and referential integrity exists between relations (tables).

It often starts with identifying the primary key of the parent releation (table). Then, traditionally, one will add the primary key field(s) of the parent table, to be foreign key field(s) of the child relations (table(s)), and establish relationship between the relations (tables) with the primary key/foreign key. For a compound primary key, the relationship will include all the fields the primary key consists of. Access will visualize that with one "relationship line" per each field the primary key consists of. So, if you really, really need a six field primary key in the CaseTotalProd... table, then structure2.jpg, displays correct relationship.

What I assume is your challenge here, has little or nothing to do with relationships, but all to do with determining suitable candidates for primary keys of your tables. I don't think you need a six field compound primary key in the CaseTotalProd... table, neither do I think you need a four field compound primary key in TechTable. Keep it as simple as possible. One field PKs are the easiest to work with, but of course (at least for some of us) the need for compound primary keys arise, but they can be PITA if one needs to propagate them through child tables...

Since you use the InitialID field in your relationship between the InitialCallTAble and the TimeTable, then why do you also have the ClientID as part of the primary key of InitialCallTAble?

Since you use the AppointmentID field in your relationship between the TimeTable and the TechTable, then why do you also have the ClientID and InitialID as part of the primary key of TechTable?

...

For Access to recognize these relationships as 1:M, it means InitialID in the InitialCallTable, AppointmentID in TimeTable, TechID in TechTable and SaleID CaseTotalProd... table each are unique (Indexed Yes no dupes) - then make each of those that fields primary key in their respective tables, you have no need for these compound primary keys in your structure.

Here's a rather good article on the fundamentals http://r937.com/relational.html

 

[Compressor]

Thanks for your reply roy. Good thing you point out the difference between relation and relationship, because I just use those words as if they were the same. My english is kind of ok, but in this case the language barrier becomes painfully clear once again.

About the amount of PK's and relations: in the working model I have used single PK's. It's no challenge really... it's like a ehm... figure of speech: staircase. I do include all other fields all the time and just query for them. If applicable the values in the selected record are assigned to whatever needs them, which is just more work... but because of that, creates new problems and insights so I learn more.

I do keep all previous values in the tables for now which isn't really necessary, they are just there. But I had set it up that way, just for me to get a clearer picture of how things "flow" from A to Z. I know if referential integrity is turned on there really is no need to do that for practical reasons in my case.

As far as the compound PK's and the multiple relationships are concerned in this situation: I'm just playing around with the PK's and relationships between tables to try and understand how it can be of use in that way. But... without succes. I guess I will need to take another look at northwind or maybe create yet another example DB for myself to see what does what. It would just be nice if the thing had a practical use too rather than just being a.... well, nothingness. For motivational reasons. Because I'm noticing that is starting to become an issue for me.

I will read the link you posted and see if that helps me too. If not, I'll get back to this topic again