By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Discussion: About Relationships

100+
P: 759
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 ?
Mar 20 '12 #1

✓ answered by 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,

Share this Question
Share on Google+
9 Replies


P: 91
Here Business indicates Business areas (different departments) and therefore many to many relationship.
Mar 20 '12 #2

100+
P: 759
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.
Mar 20 '12 #3

NeoPa
Expert Mod 15k+
P: 31,417
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.
Mar 20 '12 #4

100+
P: 759
:) 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 !
Mar 20 '12 #5

NeoPa
Expert Mod 15k+
P: 31,417
Pleased to help Mihail.

Indeed I know how you struggle with English, but at least you do. You make the effort, which is commendable.
Mar 20 '12 #6

mshmyob
Expert 100+
P: 903
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,
Mar 20 '12 #7

NeoPa
Expert Mod 15k+
P: 31,417
That's a great post and lays out the situation very clearly - especially for those learning how to work with relationships. Nice :-)
Mar 20 '12 #8

100+
P: 759
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 :)
Mar 21 '12 #9

NeoPa
Expert Mod 15k+
P: 31,417
: - )
Mar 21 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.