Many to Many Question Regarding Normalization (1 Viewer)

DKoehne

Registered User.
Local time
Today, 14:51
Joined
Apr 10, 2017
Messages
49
I use Access as my frontend and MySQL as back end.

I have a main table called client t1.
Clients have many Accounts t2 (1:M) between t1 and t2.
The accounts have Creditors t3 -technically a (Many Accounts :Many Creditors) relationship between t2 and t3.

Between the accounts and creditors there is currently no bridge table. I am using row source query to pull the t3 result and update it to t2. That is working fine albeit a bit slower than preferred.

According to 3NF and relationships - it is suggested that there should be a bridge table between t2 and t3.

My question is this in two parts:

A) If I create the bridge table (for normalization) what will that get me since it's already working (what is the biggest reason to do this here?)
What is the case to be made here for the junction or bridge table that is compelling in this scenario?

B) If I don't create the bridge table how will that hurt me ?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:51
Joined
Feb 28, 2001
Messages
18,730
The accounts have Creditors t3 -technically a (Many Accounts :Many Creditors) relationship between t2 and t3.

You say there is no bridging table between t2 and t3 yet claim this to be an M:M relationship. The thing is, you claim it is working now. I am NOT questioning whether it does what you want it to do. But I have to say, I doubt it is working like it SHOULD work as an M:M situation. It can't be doing some of the nice things that JUNCTION tables do for you. But my question is, do you really have multiple creditors with a single account? I.e. the holder of the account owes many people at once for a single account? This is a rather aggressive distribution of liability. What industry is this?

What you get with a junction table is this: With appropriate JOINs (that can be managed by the Access query wizard if you have defined the right relationships), you can use the junction to list all accounts associated with a given creditor or all creditors associated with a given account (as two different reports).

What you get if you don't create that table is that one of the two reports I named will not be possible without complex relationships that might as well be converted to a junction table anyway.

The key to understanding a many-to-many relationship is the same as understanding a 1:M relationship. If you have a key field on the 1 side and a foreign key field on the M side then the "child" / "dependent" records can all point directly to their (single) parent. Because you have one value (on the 1 side) and many value slots (on the M). To do an M:M relationship, you have to see it as needing a place for multiple slots for pointers in and from both directions. The junction table is the list of slots that will enumerate the relationships.
 

zeroaccess

Active member
Local time
Today, 16:51
Joined
Jan 30, 2020
Messages
631
If each client can have many accounts
and
Each account can have many creditors
and
You don't have a direct relationship between clients and creditors,

That isn't a M:M, it's just two 1:M relationships.
 

DKoehne

Registered User.
Local time
Today, 14:51
Joined
Apr 10, 2017
Messages
49
You say there is no bridging table between t2 and t3 yet claim this to be an M:M relationship. The thing is, you claim it is working now. I am NOT questioning whether it does what you want it to do. But I have to say, I doubt it is working like it SHOULD work as an M:M situation. It can't be doing some of the nice things that JUNCTION tables do for you. But my question is, do you really have multiple creditors with a single account? I.e. the holder of the account owes many people at once for a single account? This is a rather aggressive distribution of liability. What industry is this?

What you get with a junction table is this: With appropriate JOINs (that can be managed by the Access query wizard if you have defined the right relationships), you can use the junction to list all accounts associated with a given creditor or all creditors associated with a given account (as two different reports).

What you get if you don't create that table is that one of the two reports I named will not be possible without complex relationships that might as well be converted to a junction table anyway.

The key to understanding a many-to-many relationship is the same as understanding a 1:M relationship. If you have a key field on the 1 side and a foreign key field on the M side then the "child" / "dependent" records can all point directly to their (single) parent. Because you have one value (on the 1 side) and many value slots (on the M). To do an M:M relationship, you have to see it as needing a place for multiple slots for pointers in and from both directions. The junction table is the list of slots that will enumerate the relationships.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:51
Joined
Feb 19, 2002
Messages
30,614
I'm not sure what the last post is all about. It is simply a quote of Doc's previous post. If each account can have many creditors, then the ONLY way this will work is if you create a junction table and like Doc, I am curious as to what business you are trying to model. When I have a credit card or a car loan or a mortgage, each account has one and only one creditor. Perhaps if you explain your business, we might be able to give you more specific guidance.
 

Cronk

Registered User.
Local time
Tomorrow, 09:51
Joined
Jul 4, 2013
Messages
2,462
One business example I've recently come across where this could apply is peer to peer lending. The business takes deposits of any amount from investors and can supply a larger loan requirement by putting all or part of individual deposits towards the satisfying the loan amount.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:51
Joined
Feb 28, 2001
Messages
18,730
Ah, that would make sense in this context, Cronk - but we need the OP to tell us what he is trying to do.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:51
Joined
Jan 23, 2006
Messages
13,472
DK,
Perhaps you could describe your proposed set up using an example to highlight the main business rules. Please use simple plain English so readers can understand your requirement in context.
Good luck.
 

Users who are viewing this thread

Top Bottom