Creating a 'lookup table' utilizing content types to access IDs of other tables. Looking to ensure I have the correct model structure for what I am going for. Diagram included

Hello, so I am trying to go forward and essentially create a robust lookup table that allows for multiple users to own certain aspects of the process.

The intent is to ensure multiple tables from different areas are ‘grouped’ accordingly and matching the other tables at the end of it to produce a power BI or visualization.

The problem is usually someone doesn’t get what they’re doing, or we get another manager that wants to change the names of things.

Please let me know if there’s any redundancy. I think there is with the LookupTable. I think I can query through where its foreign keyed.

Essentially we have 3 user types here.

  • Project Owner (This person will create a ‘project’, choose which source datasets it will lookup against, and what the options for grouping are)

  • Source Dataset Admin (This person will use a webform to analyze the

    • IDs of the source datasets
    • Group up those IDs into their respective areas set by the project owner
  • SQL Developer and analyst (This person will pull this singular lookup table into a Power BI and filter it down to ProjectLookup specific, so they get all they need in one table.)

Abstracted Table relationship diagram

Example table relationship diagram

Web form goal to produce lookup table (in red of example above)

Any help is greatly appreciated. This one has been tough to wrap my head around.