Discussion: About Relationships

[Mihail]

NeoPa:
This is a question that was asked in another thread (Populating Junction Table & Many to Many Relationship). Please understand this (hijacking) is not allowed on this site.

Only to learn (teach) myself:

For me sound as one to many relationship.
One business with many policies.
Am I wrong ?

[sg2808]

Here Business indicates Business areas (different departments) and therefore many to many relationship.

[Mihail]

I am not so sure, SG.
I think is only a problem with view points.

If I can define, for a certain Department, a list with the policies using by that Department means that I have a relation One Department to many policies.
but
If I can define a list, for a certain policy, with Departments that uses that policy means that I have a relation One Policy to many Departments

So, in my opinion, this is a One to Many relationship. The direction (vector) is a choice to made at design time.

This is the reason I ask the experts.
Hope they can answer me from the database view point not from your reality view point.

[NeoPa]

Mihail:
Am I wrong ?

Yes. Absolutely. Unless your English is really bad today then this is just wrong.
I would add here though, that any understanding that you might have been disrespectful of anyone in the thread is mistaken. I know that was merely down to difficulties with the language.

Many-to-Many (M:N) relationships are often required and the original question very much appears to be a good example of such a requirement.

Implementing such a relationship is typically done with a 'bridging' table, as illustrated clearly by Mshmyob in his post #2 of the linked thread. Such a table includes FKs to both tables within the M:N relationship. Each of the FKs is connected to the other table using a One-to-Many (1:M) link. Only together are they considered to reflect an M:N relationship.

[Mihail]

:) No NeoPa.
My English is as you know.

I fully agree with the "bridge" table. No doubt.

@NeoPa:
Each of the FKs is connected to the other table using a One-to-Many (1:M) link. Only together are they considered to reflect an M:N relationship.

This enlightened me and show (to me) my mistake.
Also is first time I have a REAL explanation for Many to Many relation ship. Finally I understand that !
This is why I wanted to hear an explanation from an expert in databases.
Thank you a lot !

And thank you for this new thread too !

[NeoPa]

Pleased to help Mihail.

Indeed I know how you struggle with English, but at least you do. You make the effort, which is commendable.

[mshmyob]

A simple way to determine the relationship type between tables is like so:

You MUST always have 2 rules for each set of related tables. I like to call them bi-directional rules.

So in this case the 2 tables tblPolicy and tblDepartment are related to each other. The question is "how are they related?". To determine that create 2 rules that go in opposite directions like so (always start the rule with the word ONE)

From tblPolicy ---> tblDepartment
ONE policy can be applied to MANY departments (1:M) - Notice the Many side is on tblDepartment

From tblDepartment ---> tblPolicy
ONE department can have MANY policies (1:M) - Notice the Many side is on tblPolicy.

Therefore you have a MANY on the tblDepartment and a MANY on the tblPolicy resulting in a M:N relationship - bridge table required. The minimum fields in a bridge table are the PK fields from the adjoining tables making them FK's in bridge table when looked at seperately.

With a slight change to the business rules you get a different relationship type and therefore a different design. Assume the rules changed like so:

From tblPolicy ---> tblDepartment
ONE policy can be applied to ONE department (1:1) - Notice the ONE side is now also on tblDepartment

From tblDepartment ---> tblPolicy
ONE department can have MANY policies (1:M) - Notice the Many side is on tblPolicy.

You therefore now have a 1:M relationship between tblDepartment and tblPolicy and no bridge table is needed. The foreign key goes in the tblPolicy table (note: FK ALWAYS goes to the MANY side).

cheers,

[NeoPa]

That's a great post and lays out the situation very clearly - especially for those learning how to work with relationships. Nice :-)

[Mihail]

Indeed, NeoPa, even if he say the same as you.
I am sure you agree that mshmyob's post is the really best answer.

Until now :)

[NeoPa]

: - )