Need advice on the Best Way to Organize the tables in the Inspection Base for a List View

[Drew_Nemer]

I am building a new base for my company's Inspectional department to help the coordinators input and track historical, ongoing, and schedule inspections for our housing agency's low-income housing vouchers.

We are tracking a combination of inspectional protocols: 

1. Move-In - When a person moves into a new unit
2. Annual - The yearly inspection for an ongoing participant
3. Special inspections. 

I broke it down into five components (tables) to reflect the relational data that I want to connect with bullets that represent the fields

1. Participant (Voucher holder):  
   • Last Name
   • First Name
   • Household ID
   • Language Preference
   • Phone Number 
   • Email
2. Property Owner
   • Owner Name
   • Owner ID
   • Owner Phone
   • Owner Email
3. Units
   • Street
   • City
   • ZIP Code
   • # of Bedrooms
   • Type (i.e., Low-Rise, High-Rise, Townhouse, etc)
   • Year Built
4. Inspections
   • Protocol (Annual, Move-In, Special)
   • Requested Date
   • Scheduled Date
   • Outcome (Pass, Fail, Reschedule, Cancelled, etc)
   • Inspectional Report Attachment field
5. Inspectors
   • Inspectors 

I am trying to create a multi-level List View in Interface Designer that will act as a way for the Inspectional Coordinators to be able to look-up, assign, create, and review the data..

THE ISSUE:

Participants or Voucher holders can be on the program for many years and can live in many different units, so Units and Participants need to be in separate tables because a Participant can live in different units, and there are cases where a unit housed different participants at different times.

Same goes for Property Owners.  Most property owners own multiple units, and it is also very common for owners to sell units and change, so that needs to be reflected. 

And with Inspections, over time there are many inspections and we want to keep track of them all.

The difficulty is figuring out how the Coordinators can easily create new inspections with the option to create or select different units and/or property owners.  Importantly, being able to look up existing PO and Units to assign in case they are already in this new database. 

Here is what I have come up with so far within the Interface Designer:

Level 3 is the Tenant, and Level 2 is the Unit.  Property Owners are a linked field in the Units that can be looked up.   

Underneath units is the Inspection.

The issue is that overtime it is possible that Inspection history would be moved to different tenants as they change.  

I keep coming to this situation that not only should Participants Table be linked to Units, Units to Owners, Units to Inspections.  But Participants should be linked to Inspections, Owners to Inspections.   

For people using the base to track and schedule inspections, having coordinators required to manually link different tables in multiple directions seems too tricky and rife for mistakes.   

Perhaps there is an automation concept that might make it easier, or some other method.  But I am wondering if anyone else has any insight on how to properly manage these tables and create a workflow that doesn't require zig-zagging table links.  

One idea I have had is to create a Main parent table that has all the fields of the tables below and automations that both allow looking up records as well as creating new ones that would be automated to input in the tables behind.

[Ben_Young1 · Accepted solution]

Hey @Drew_Nemer,

I'm about to run out the door right now, so I don't have much time to properly talk you through this, but I was intrigued by your use case and built a small demo of how I think you might want to think about structuring your data.

Here's a link to the base I built.

There's also an Interface I built that provides you a peek at how cleanly all the data fits together.

At the top level are your individual units.

I created a table called Residencies. These records allow you to create a historical picture of all the previous occupancies for a given unit.
The residency records are the second layer of the list hierarchy:

Inspections are at the bottom layer of this list:

Feel free to play around with this as you see fit.
Don't hesitate to pop it open and duplicate it into your workspace and peek at the Interface I tossed together.

I have no doubt that you will have more questions than answers, so toss together any questions you have about what I built and I'll come back at you with a full breakdown and walkthrough on why this structure might be better suited for your needs and will also facilitate a more robust user experience for your users.

[Drew_Nemer]

Wow you really understood this very well.  Thank you so much! this really makes a lot of sense