Forum Discussion
Linking multiple tables via a single mapping table
Hi pgarman,
To clarify, can stakeholder and functional requirements have no parent? In other words, must a stakeholder requirement have a business requirement and must a functional requirement have both a business requirementa nd a stakeholder requirement?
To me, it seems like the main issue is there is no proper hierarchy in the table that holds all the relationships. It would be a lot simpler to do if the answer to my above question is yes, but I would first self join the table to itself twice to create a proper hierarchical relationship table.
For example, if the original table looks like:
| fromItem | toItem |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 1 | 5 |
| 1 | 6 |
I would convert it to look like:
| Business Requirement | Stakeholder Requirement | Functional Requirement |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 5 | |
| 1 | 6 |
This table could then be joined to each of your requirements tables, so you would have a total of four tables and three relationships. If this doesn't make sense to you or is not feasible as a solution, please share an anonymized sample pbix file so I can better explain or see why that wouldn't work. (If you're not sure how to do this, please check out the pinned thread in this forum.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Wilson_ thanks for this tip.
To answer your first question, the relationships are not required - so it is possible, for example, that a Stakeholder Requirement would not be connected to an upstream (or parent) business requirement.
This is also a 'many to many' data model, so here are some scenarios by 'item type':
- A Business Requirement might have 0, 1, or multiple 'downstream' related Stakeholder Requirements.
- A Stakeholder Requirement might have 0, 1, or multiple 'upstream' related Business Requirements.
- A Stakeholder Requirement might have 0, 1, or multiple 'downstream' related Functional Requirements.
- A Functional Requirement might have 0, 1, or multople 'upstream' related Stakeholder Requirements.
In other words, the relationship model is not a linear tree - the branches might intersect or merge at different levels.