I need some advice with this data model. Maybe this approach is flawed but is there a way to make this (simplified) structure work? I can't get all the relationships at the same time due to ambiguity:
My desired logic:
Example use cases might be:
Thank you in advance,
Thank you for the suggestion, but this would create many duplicates in the Hazard table which makes any analysis on that table much more complicated. The other way I considered Is having two Location tables, but that has problems too, especially as I want to use it for RLS.
This dataset is getting published to users who are report builders rather than data modellers and they will just get frustrated with duplicates and/or having to filter location in two places.