The issue I have is that no relationship arrangements fully satisfy what we're attempting to achieve. Current scenarios I've attempted or researched:
Option A: Create Resources (Parent)-Properties (Child) relationship limits only one resource a la one-to-many. We have two or three resources per property which would need to be a parent.
Option B: Create a field or category(ies) in the Properties tables on which to filter related child records (e.g., Incidents) when reporting. This would require manual changes should the Resources and Assignments records change. Too much data management. Also, use of formulas to automate is not feasible from the research I've done (e.g., cannot use across tables upstream).
Option C: Scrap the Properties-Incidents relationship and/or add an Property Assignments(Parent)-Incidents(Child) relationship. This would require too much management of data for the multiple Property Assignments.
Option D: Create new Staff Regions Tables. They would be related: Properties(Child)-Staff Regions(Parent to Properties/Child to Resources)-Resources(Parent). This requires additional data management. E.g: if a Property Assignment changes, the Staff Regions table(s) would also have to be updated (duplicative).
Option E: Similar to "D", create Staff Regions table as a parent to Properties, child to Property Assignments. This makes a circular relationship (is this a problem?):
-Properties(Parent to Property Assignments/Child to Staff Regions)
-Staff Regions(Parent to Properties/Child to Property Assignments
-Property Assignments(Parent to Staff Regions/Child to Properties)
Option F: Replace Property Assignments table with a table for each Assignment (e.g., Accountant, Property Manager etc.). Searching assignments by property is hindered by this setup, and would only be visible in the Properties table.
Option G: Reverse the relationship of Properties and Assignments. Not possible given the one-to-many nature of the relationship.
Below is the best I could come up with brainstorming and thinking through each scenario!
