Skip to main content

Hello Airtable Community,

I'm in the process of designing a custom Business OS for my construction project management company, and I've reached a critical architectural decision point. I'd love to get your expert feedback on the best way to structure our Project Team roster.

The Goal:

For any given Project, I need a single, clean, and scalable way to see the entire team involved. The challenge is that our teams are a mix of:

  1. Internal Employees: (e.g., our Project Manager, Principal, Dev Engineer) who are managed in a dedicated Team Members table with specific internal data like their job titles. It is my understanding that it is best practice to keep your employees in a separate table from the standard Contacts CRM table.

  2. External Stakeholders: (e.g., the Client's Property Manager, an external MEP Engineer, a contact at an incentive program) who are managed in our main Contacts table, which is linked to a Companies table.

My top priority is to create a system that is simple for my team to use ("less clicks, less brain damage") and provides a single, unified view of the entire project team, regardless of whether a person is internal or external.

The Options I'm Considering:

I see three potential ways to model this, each with pros and cons. I'd love to know what the expert community considers the best practice.

Option 1: The "Polymorphic" Junction Table (My current leaning)

  • Create one Project Team table.

  • This table would have two separate, optional linked record fields: one for Internal Team Member (linking to Team Members) and one for External Contact (linking to Contacts).

  • I would then use a series of "helper" lookup fields and final "smart" formula fields to create a single, unified Name, Company, and Title column for a clean user experience.

  • Pros: Provides a single, unified view of the entire team. Scalable.

  • Cons: The backend setup is a bit "wonky" with several helper fields.

Option 2: Two Separate Junction Tables

  • Create two distinct tables: Internal Project Team and External Project Team.

  • Pros: Very clean data structure. Each table is perfectly tailored to its purpose.

  • Cons: To see the entire team for a project, my users would have to look in two different places, which feels inefficient.

Option 3: The Hybrid Model

  • Add 3-4 dedicated linked record fields directly to my main Projects table for the most common internal roles (e.g., Assigned PM, Assigned Principal).

  • Then, use a Project Team table just for all the external stakeholders.

  • Pros: Quick at-a-glance view of key internal players.

  • Cons: Seems less scalable if new internal roles are added, and it splits the "team" information across multiple places.

My Questions for the Experts:

  1. Is Option 1 (the "polymorphic" approach with helper/formula fields) the standard, recommended best practice for solving this common problem in Airtable?

  2. Are there significant performance or scalability downsides to this approach that I should be aware of?

  3. Is there a simpler, more elegant fourth option that I haven't considered?

Thank you in advance for your time and insights. I'm trying to build a robust foundation, and getting this "people" part of the architecture right is critical.

Hey,

 

Might depend on your specific needs, but I would probably go for:

Having all contacts (internal and external under one unique table. You can differentiate them with a Type or similar field). 
Having one unique Project Team table, with only one unique linked record field to Contacts. 
 

The above is lean and scalable. I’m on my phone en but I’ll try to get back to you with more detail. 

Feel free to grab a slot using this link if you’d like to go through it together!

 

Mike, Consultant @ Automatic Nation. 

Hm, I think if I were you I’d have a table for ‘Project Team’ that included both internal and external people and create a single select field to label that, and separately have a ‘Contacts’ table for the CRM side of things

The external project team records that you’re storing in the ‘Contacts’ CRM never actually touch any of the CRM functionality, right?  

Thank you Adam and Mike. 

 

​@Mike_AutomaticN I think the main issue with this that concerns me is that the kind of data I want to store about Employees is going to be different than Contacts. So I could create Employee Contact records in the Contacts table, but it might be kind of awkward.

​@TheTimeSavingCo The project team table needs to have fields like name, title, phone, email. It also needs project specific context fields like role (as it relates to the persons role on the project.)

Hey ​@gregczb what about just having different views showing specific fields for “Internal” and “External” contacts?

 

That should probably solve the awkwardness! Right?

 

Mike, Consultant @ Automatic Nation

That could work, as long as it is possible to display the important fields for both Internal and External Contact in a Project Teams view on an Airtable Interface. I am still new to Interfaces so not sure what is possible.

re: The project team table needs to have fields like name, title, phone, email. It also needs project specific context fields like role (as it relates to the persons role on the project.)

Yeah then I’d definitely keep all the team members in a single table, regardless of whether they’re Internal or External, and use a select field to label them as such

Terms & Conditions