Database Design Question

[paulrockliffe]

I sort of know what I'm doing, but occasionally I hit an issue in defining the relationships between entities that I'm not sure how best to fix.

I'm designing tables in Excel and then adding them to PowerBI and defining relationships there, I know that's not a normal database solution but my issue is a database design issue.

I'm not experienced enough to see immediately what the right solution is, I have a couple of ideas but I'm hoping this is obvious to someone.

I have a large list of stakeholders on a table, these people can be brought onto Projects in different roles. I have a table that allocates these people to a project and assigns a role, eg sponsor, subject matter expert etc. That all works fine.

Against each project I have separately linked a business area, which is where the project sits. Each of these business areas is owned or managed by people that are in my list of stakeholders. I want to use these stakeholder's details against my projects in reports that are centred around the business areas.

If I add a relationship between my table of stakeholders and my table of business areas PowerBI complains because it then has two routes between each Project and it's Stakeholders.

Any clues?

[Crasher242]

First thought would be to normalise out the "role" and the usage of that role

If you have a "roles" table that has things like SME, Stakeholder etc
You then have a role usage table, which has foreign keys for the Role, the person and the project
So Bob Smith is an SME on one project and a Stakeholder in another, you'd have...

Bob Smith exists once in the Person table
In role usages you have a row linking Bob to the SME Role and to Project X, and you have a row linking Bob to the Stakeholder role to Project Y

Any use?