Complex many-to-many relationship

[wardus]

Hi All,

I'm a bit stuck with a relationship im trying to put nicely into access.
I haven't used it for quite some time, so I need some guidance, which is much appreciated!

I have 10 companies, which have 1 or more problems (max 10). These questions are addressed by 1 or more solutions (max 10).
What makes it complex is that a problem can use several solutions, but a solution can also be used to several problems.

So for example:

Company X has problem 1, 2 and 3. And uses solution A, B, C and D.

Problem 1 is addressed by A
Problem 2 is addressed by A, C & D
Problem 3 is addressed by B & C



But the next company can have different solutions for the same problems, or different problems for the same solutions (this isnt set)

How can i best put this into a database? Do i have to make a different table for each problem where i checkbox the solutions a company uses

Hopefully my problem is clear, it isnt a straight forward many to many relationship where i can just combine the primary keys.


Thanks for your time in advance, any help means a lot,

Wardus

[June7]

You have standard problems and standard solutions? Consider:

tblCompanies
CompID (PK)

tblProblems
ProbID (PK)

tblSolutions
SolID (PK)

tblCompanyProblems
ID (PK)
CompID (FK)
ProbID (FK)

tblProblemSolutions
CompProbID (FK)
SolID (FK)

[orange]

This is my understanding of your post.

A Company has 1 or more Problems
A Problem can have 1 or more Solutions
A Solution may fix 1 or more Problems

Is that close to your understanding?

[wardus]

This is my understanding of your post.

A Company has 1 or more Problems
A Problem can have 1 or more Solutions
A Solution may fix 1 or more Problems

Is that close to your understanding?

Thats right! That summary would save me a lot of explaining

[wardus]

Thanks for the input! Will try this asap and let you know if i encounter problems. If anyone has something to add, or additional tips please lets me know

[Xipooo]

I have a video explaining Many-To-Many relationships that might help.

http://youtu.be/d5mQYTVaq7c

[wardus]

I have a video explaining Many-To-Many relationships that might help.

http://youtu.be/d5mQYTVaq7c

Thanks! helped me to get back into access, it has been around 5 years, so i could use the refreshment

[wardus]

You have standard problems and standard solutions? Consider:
...
tblProblemSolutions
CompProbID (FK)
SolID (FK)

June7, thanks alot for your time and effort! Since there are standard problems and standard solutions this helped me perfectly.

Tthere is however another level of data. The solutions consists of standard "steps" which are similar to the problem - solutions many-to-many relationship.
(So 1 solution is formed out of 1 or more steps, and 1 step can be used for 1 or more solutions)

Could i integrate this into the above mentioned solution using exactly the same type of tables?

[June7]

Yes, I would think so.

tblSteps
StepID

tblProblemSolutions
ID (PK)
CompProbID (FK)
SolID (FK)

tblSolutionSteps
ProbSolID (FK)
StepID (FK)

[wardus]

Yes, I would think so.

tblSteps
StepID

tblProblemSolutions
ID (PK)
CompProbID (FK)
SolID (FK)

tblSolutionSteps
ProbSolID (FK)
StepID (FK)

Oke June7, so far it has worked as a charm (thanks once again)
But lets say that for each problem per customer there can also be different analytics.
Again 1 problem can have several analytics and 1 analytic can be assigned to several problems.

So let me try it:

We already have:
tblProblem
ID (PK)
ProbID

tblCompany
ID (PK)
CompID

TblCompanyProblem
ID (PK)
ProbID (FK)
CompID (FK)

In addition we create:

tblAnalytics
ID (PK)
AnaID

tblAnalyticProblems
ID (PK)
ProbID (FK)
AnaID (FK)

tblAnalyticProblemsCompanyProblems
AnalyticsproblemID (FK)
CompanyProblemID (FK)

Or is there an easier way of solving this, since there might be another many-to-many variable added in the future.

[wardus]

I might have thought of a simpler way.

By making an extra table which lists:
1 = solution
2 = manager
3 = etc...

And then i can use the old table setting that i have and simply adding Type, which then refers to the type of entry.
This way i wont have to make a seperate connection for the new variables

[June7]

Maybe. You do know your data best and it sounds like you have an approach you are comfortable with. When you have the data structure 'fleshed out', can post ERD if you want more review.

[wardus]

Oke. I will first work it out more detailed before asking for additional help.
Thanks again, this forum turns out to be extremely useful