One to many with many to many relationships (1 Viewer)

jaryszek · Aug 10, 2018

Hi Guys,

i created simple relationhips and i wonder it is ok:

https://ibb.co/cFLQd9

We have many to many relationship between Tasks and Customer table.
But Customers can have multiple cars. So it is one to many relationhips like here.

IT is good appraoch to have Customer_ID as a part of 1:M and M:M relationhips?

Thank you for help,
Best,
Jacek

Frothingslosh · Aug 10, 2018

While some probably will, most people are going to be unlikely just to click a random link. I suggest taking a screenshot and attaching that to (or embedding it in) your post.

jaryszek · Aug 10, 2018

Frothingslosh said:
While some probably will, most people are going to be unlikely just to click a random link. I suggest taking a screenshot and attaching that to (or embedding it in) your post.

thanks added

Jacek

jdraw · Aug 10, 2018

Customers can have multiple cars.

Can a Car be owned/shared by multiple Customers?

Frothingslosh · Aug 10, 2018

Okay, so each task can have many customers, and each customer can be part of multiple tasks?

Then t_junction is more or less right. I would recommend placing a unique index on CustomersIDFK and TasksIDFK if you haven't already. Also, there's really no need for the FK designation, nor would I pluralize the ID fields. Have them the same name as the primary key they tie to, and that is sufficiently intuitive.

As to the cars, assuming no car is shared between customers, then your relationship layout is correct. If customers can share cars, then as jdraw hinted, another junction table will be needed.

However, to answer your question, it's perfectly fine to have a key that's part of a M:M relationship ALSO be part of 1:M relationships. You'll have to be careful with querying or you could get some fairly nightmarish recordsets returned, but overall it's perfectly acceptable.

Edit: I'm attaching an example from one of my projects. While this is FAR from the total relationship structure, it's enough for here. Note that RightsLevels is actually part of two different M:M relationships, as well as a 1:M relationship with the Users table.

jaryszek · Aug 10, 2018

Frothingslosh

You are the boss. Thank you. Clear for me.

This i am not understanding:

I would recommend placing a unique index on CustomersIDFK and TasksIDFK if you haven't already.

why to do that? What would be the purpose of it ?

In your example you are using also 2 PK in ReportVisibility and Rights table.
Why?

Can you explain as simple as you can?

Best,
Jacek

Frothingslosh · Aug 10, 2018

It keeps one customer from being listed twice in the same task, or the same task listed multiple times for the same customer. Obviously, if there are other things I'm not aware of (like a task being done repeatedly), then that might not be the way to go, but the idea is to keep the records in the junction table unique.

What you're seeing in my screenshot is one multi-field primary key. Using rights as an example, each rights level can have multiple permissions (RightsEntries), and each permission can (and does) appear on multiple rights levels, but every paring of rights level (Admin, Read-Only, User, Supervisor, etc) and rights entry must be unique. I don't want two Administration Screen (for example) permission entries for, say, Supervisors.

The idea behind a junction table is that it allows many to many relationships possible, but each record still needs to be unique. In my case, LevelID and EntryID form what is basically a natural key. It's possible to have a junction table without the pairings be unique, but there really needs to be a special reason for that.

jdraw · Aug 10, 2018

No, these are not 2 primary keys --they represent a compound primary key. 1 PK that is made up of 2 or more fields.

Here is a link to a composite unique index.
http://www.geeksengine.com/article/composite-unique-index-access.html

Frothingslosh · Aug 10, 2018

Thanks, I couldn't for the life of me remember the actual term for it.

jaryszek · Aug 11, 2018

Hi,

thank you Frothingslosh. I am not feeling this yet but your explanation is good enough.
I added composit key and indexes without allowing to input duplicates.

Now I can have one customerIDFK = 1 and TaskIDFK = 1. But when i want to in new record add CustomerIDFK = 1 there is an warning that i can not do this.

Hmm how to set up this to have possibility to allow customer to do 2 different tasks but not permit to do task repeatedly.

Best,
Jacek

Uncle Gizmo · Aug 11, 2018

I blogged about Many to Many on my website here:-

http://www.niftyaccess.com/many-to-many-relationship/

Basically, Many to Many doesn't exist! It is always two "One to Manys"....

I show examples of both sides of a "Many to Many" and also explain a couple of problems along with the solution...

The code sample is currently free, just sign up for my news letter...

Sent from my SM-G925F using Tapatalk

jdraw · Aug 11, 2018

jaryszek,

In t_junction, the field ID is your PK.
You make a unique composite index using your
CustomersIDFK and TasksIDFK fields.

This link shows you how to set up unique composite index.

jaryszek · Aug 11, 2018

thank you jdraw.

I am wondering - these composit key in your example. There is only one PK.
There is no 2 PKs like in Frothingslosh model. Why?

Working!
Best,
Jacek

The_Doc_Man · Aug 11, 2018

But when i want to in new record add CustomerIDFK = 1 there is an warning that i can not do this.

That should be legal as long as the TaskIDFK is NOT 1 for the new record.

However, I think you have TOO MANY fields in your table's primary key (PK). The screen shot shows us a field called ID which is an autonumber AND a participant of the PK. For the junction table, you would have no ID field because the other two components would be the PK as a compound key. As a PK, they would by definition be "No Dups" so that you would never be able to enter the same combination twice.

The way you had it implemented in the screen-shot, you could have a thousand records with the same Customer and Task numbers since third item in the PK was an autonumber that pretty much guaranteed that the combination would always have one unique part - the ID field.

Now, the other part of your comment: It would be fine to create a record with Customer 1 and some other Task, but in a "true" junction table, having the Customer 1/Task 1 combination should never occur more than once. If your business REQUIRES that you do this then you have not fully explained your business rules. And it would imply that a very different layout would be required.

jaryszek · Aug 11, 2018

Hi The_Doc_Man,

thank you.

but in a "true" junction table, having the Customer 1/Task 1 combination should never occur more than once

Why this is a "true" junction table? So having repeatedly pairs (when your business wants to) it is not a correct approach?

Best,
Jacek

Frothingslosh · Aug 11, 2018

Also, one thing you're missing: My tables don't have two primary keys. They have one primary key with two fields in it. The links the others have given you show how that's done, and Doc just explained why: so that each pairing can only occur once.

jdraw · Aug 11, 2018

jacek,

There are never 2 PK's in a table.
There may be 2 or more fields used to make a compound primary key -and I think that is where you are confused. It is still a single primary key that just happens to be made up of multiple fields.

A Primary key is a field or fields that uniquely identify each and every record in that table.

Normalization rules would tell you to remove repeating groups.
So your

So having repeatedly pairs (when your business wants to) it is not a correct approach?

Click to expand...

would say that such repeating pairs would represent a non-normalized structure.

Bottom line:

A Primary key can be composed of more than 1 field. Every table should have a Primary key.
A Primary Key may be a single field, or may be composed of multiple fields (in Access I believe the maximum is 10 fields). However, whether a single field or multiple fields it still represents a single Primary Key.

As Doc pointed out, if you have a junction table to "represent a M:M relationship", you can
-use a compound PK made up of the PK of each of the tables requiring the junction construct OR
-use an autonumber single field (surrogate) PK, and if you do,it is recommended to use a composite unique index to prevent duplicates.

If you use the compound PK (made up of the PK of each of the tables requiring the junction construct), you may find the structure becomes confusing/awkward if that junction table is involved in other junction tables. Many Access developers use a single field autonumber PK for every table --this is not a necessary rule, but it is a consistent technique for those who use it.

Also, a comment on some other things I notice in your posts. A data model with tables and relationships is a reflection or implementation of your business rules. In my view, you can not have arbitrary tables and relationships. You start with a problem/issue/opportunity and identify the requirements and the rules within the "business".

Any way good luck with your project.

The_Doc_Man · Aug 11, 2018

So having repeatedly pairs (when your business wants to) it is not a correct approach?

If a particular set of pairs repeats, then something else is missing from your description.

First, if the business rules say they CAN repeat, then fine. When the business itself and the business model disagree, ONE of them is wrong, and it is usually the model as represented by the Access application, because this is pretty much a hard rule that might as well be etched in stone: The business drives the model; the model does not control the business. In the USA we sometimes talk about the "tail wagging the dog" as an idiom for "having priorities backwards in the way things work." If the dog is your business and your Access app is the tail, the tail must never wag the dog.

Second, if the business requires repetition, then the question is WHY? (That's not me being snarky; it is an EXPLORATION of the business.) When you have such a repetition, what makes it different from the OTHER instances of repetition? THAT last question is CRUCIAL to understanding your model. Is it a date tag? Is there a third factor to provide uniqueness? If so, that third factor is not discussed (yet) in your thread.

The idea is that you want to be able to tell the difference between two table entries that at first glance would be superficially similar. The point of recording multiple entries is that it MEANS something in your business. And that hypothetical third field would tell us WHAT it means. I hope I'm making this clear for you. That is why our advice might be a bit confusing. That usually happens when we have an incomplete picture of your end goal.

jaryszek · Aug 13, 2018

Hi Guys,

thank you for such a nice explanation and examples.
Now all is clear for me!

Best,
Jacek

One to many with many to many relationships (1 Viewer)

Registered User.

Attachments

Premier Pale Stale Ale

Registered User.

Super Moderator

Premier Pale Stale Ale

Attachments

Registered User.

Premier Pale Stale Ale

Super Moderator

Premier Pale Stale Ale

Registered User.

Attachments

Nifty Access Guy

Super Moderator

Registered User.

Immoderate Moderator

Registered User.

Premier Pale Stale Ale

Super Moderator

Immoderate Moderator

Registered User.

Similar threads

Users who are viewing this thread