Multiple Many to Many Relationships

[Guest]

I designed a traditional Many to Many application that tracks students
registration. We have two tables, tblStudents and tblClasses that are joined
via a Linking table called tblLink. That works great.

However, someone else in our agency asked me to help design an application
that appears to have multiple Many to Many relationships so I need help with
that. I haven’t completely thought out the process, but this is what I know
so far.

We need to track violations [tblViolations] which are linked 1 to Many
countries via tblCountries. In addition, each violation is linked 1 to 1 to
only one company taken from tblCompanies.

In sum, we need to track:
-----------------------------
how many countries are linked to each violation
how many violations does each company have
how many countries are linked to each company
how many violations does each country have
there are probably more, but I can’t think of them right now

In sum, we have relationships between 3 different tables and I would
appreciate any suggestions on how to design this application.

tblViolations
tblCountries
tblCompanies

Thanks,
Robert

 

[David F Cox]

It reads to me as 1 violation is by one company in one country and you can
get all of the data you require from:-

tblViolations
id_violation PK
id_company FK
id_country FK
(id_violation_type FK)?

This is considering each violation as an individual item. I suspect that
there may be a violation type that you will want to keep track of in a
separate Lookup table.

 

[Guest]

David:

It reads to me as 1 violation is by one company in one country and you can
get all of the data you require from:-

That's incorrect, that's why I said 1 violation is linked to MANY countries.
However, the other part of your statement was correct, one violation is
linked to only ONE company.

Robert

 

[David F Cox]

So if the violation is "poison in the coffee" it can be linked to "lower
Bongo Bongo" regardless of any company?

or is it as I see it "poison in the coffee" is linked to "Dodgy geezers" who
operate in "lower Bongo Bongo" and "Upper Bongo Bongo" and perhaps other
countries.

I suspect it is the latter, and that you want to generate a query showing
violations and countries in which they have occurred, which can be done with
the structure I suggested.

 

[Guest]

David:

First, you are correct, it is the latter. Second, I must be missing
something in your response.

The tables and primary keys are already designed. What's confusing me is the
fact that it appears to be a situation, where unlike my student/classes
database where 2 tables are involved in a Many to Many relationship, it
appears to me as if we have 3 tables involved in Many to Many relationships.

Am I wrong? If that's the case, that explains why I'm confused by your
response.
It sounds as if you are telling me that we can link Violation_ID to the
other two tables in a single One to Many and then a One to One relationship.

One Violation to Many Countries
One Company per Violation

Is that what you're saying?

Thanks,
Robert

 

[David F Cox]

Let us play with a simplistic version to avoid joins:-
tblViolations
id_violation PK
company text
country text
violation Text

SELECT violation, country
FROM tblViolations
GROUP BY violation, Country

I believe that that will result in a list of violations and the countries in
which those violations occurred.
There will be violations that occur in many countries, and countries that
have many violations.
we only have a many to many relationship because we are omitting the
company.

Tblviolations is the link table in the many to many relationship between
violation and country.

In practise I stand by my first suggestion:
I suggested:-

tblViolations:-
id_violation PK
id_company FK
id_country FK
id_violation_type FK
(notes memo) ?

and have three associated lookup tables with Company details, Country
details, and violation description.

 

[Guest]

Hello David:

First, let me take a moment to say that I truly appreciate you taking the
time to work with me on this issue.

Second, I'm definitely going to try your suggestions.

Third, the following statement has me confused. Could it possibly be a typo?

Tblviolations is the link table in the many to many relationship between
violation and country.

I'm under the impression you need a third table to create a link. With
Students and Classes, the linking table is tblLink. How could tblViolations
be the linking table between countries and itself? Am I missing something
obvious?

Robert

 

[David F Cox]

Sometimes I express myself in a confusing manner.

In this table we see country and violation type. There will be many
violation types and many countries. I should have been clearer in my
seperation of violation type (poison in the coffee), as distinct from an
individual case of that violation by a particular company. It is ambiguous
language, and it is the bane of many a project. :-<

This table acts as a link between violation types and countries, using my
terminology.

Good luck.