Structure for my Project/Program Management Airtable

[Micouti]

Hi All, 

I am looking for some guidance on how to structure my Airtable (whether on a single base or multiple) best program management. Currently, I am managing 6+ programs with multiple projects within them which I will breakdown for understanding

Before I outline the structure of the programs - the key goals I'm hoping to achieve are the following:

Primary goals

• Centralised source of truth for all programs
• Dashboards to show the progress of current programs (likely based on milestones)
• Show all tasks outstanding for each stakeholder
• Critical paths with date dependencies. 

Secondary goals

• Track capacity of key stakeholders across all programs
• Link records to external bases e.g. Procurement, Lab testing, Supplier sourcing
  (Use case: people submit form requests on these bases which turns into a request - would love automation to trigger this from PM bases or at least link the line item on the PM base with the new request item. Mainly to see status changes etc)
• Budget tracking
  (variation of quotes vs actual spend and categorizing that spend e.g. shipping cost, PO as these may be repeatable costs for different programs)

The Structure + Context

Level 1: The Programs

Apple

Pear

Orange

Melon

Grape

Level 2: The Program phase

E.g.

Apple Workpackage 1

Apple Workpackage 2

Pear Sprint 1

Pear Sprint 2

Melon Version 1

Orange Phase 1

Grape Phase 1

Grape Phase 2

Grape Phase 3

Level 3:

(Projects or "group of tasks" within each program phase)

NB: Some of these may be similar across the program phases and possibly even across certain programs but each is quite agile and specific so isn't exactly the same)

For example

Apple workpackage 1

- Planning

- Testing

- Production

Apple workpackage 2
- Planning

- Component 1 

- Component 2 

- Component 3

- Component 4
- Testing

Level 4: 

The actual tasks within each "Project / Group of Tasks"

E.g. for Component 1 of Apple workpackage 2

- Evaluate the current stock of Component 1

- Source external partners to provide Component 1

- Gather quotes

- Gather samples from 3x suppliers

- Receive samples inhouse

- Internal Testing on samples

- Decision on which supplier to proceed with

- Order/PO request (Internal form)

- PO sent (once the status of the request turns to done - this to also be done)

- Production begins

Etc...

Level 5: 
Further subtasks...

As you can see from above...you may want to track subtasks with each sample that comes in as internal testing may contain 4+ different steps that happen at each time across the 3 samples.

(I also understand 4 levels is probably as deep as Airtable goes)

I totally understand this is quite detailed but would appreciate any guidance on best practices with Airtable for any part of this.

Thanks in advance!

[Mike_AutomaticN]

Hey @Micouti!

This seems a bit extensive to go through it here, but I'd be happy to hop on a brief call and share my insights with you. If interested, feel free to book a call. Right after our call, we can always come back to this post to outline our conclusions!

Mike, Consultant @ Automatic Nation

[TheTimeSavingCo]

Hmm, if your reasoning for wanting to have separate bases for each program is related to concerns about the record count and exeeding it quickly (50k per base on Teams), then yeah, multiple bases is the way to go

If not, I'd try putting everything in one base instead of having multiple bases because you want centralized reporting.  It's possible to consolidate the data from multiple separate bases in a single base for reporting, but it'd be a challenge to set up and maintain

In the same vein, you mention having "Procurement", "Lab testing" etc as external bases and if possible I'd keep these in the same base too

Using "Procurement" as an example, assuming a single base with all your programs, in order to link the Procurement records to it you'd need to sync them from Procurement into that single base, and so having it as a separate base doesn't solve the record count problem.  If it's a security concern, then you could use Interfaces to restrict specific user accounts to only be able to access data in the Procurement table, so that''s fine

---
Levels 1 - 4 would be a table each I think, but perhaps try experimenting with Level 4 and Level 5 being in the same table?

You'd use a linked field to link the tasks with the sub-tasks and view it you'd use the List view and allow nested records within it

Link to base

[Mike_AutomaticN]

Following up with the above conversation, I'd like to share a couple of insights discussed on our call for any future reader of this post!

1. One or multiple bases? I usually suggest having only one base, as long as (i) scalability can be achieved without hitting record count limits; (ii) information does not need to be restricted to people who need access to the base itself (not interfaces) for dev purposes.
2. Architecture. One or multiple tables for tasks and subtasks? One table linking tasks among each other would be super dynamic as it would allow for n number of levels without the need of making changes to architecture. However, when linking records within one same table, Airtable does not automatically create backlinks -this should be handled via script. If the amount of levels is known in advance (e.g. you'll never have more than 3 levels: Main Task; Sub-Task and Sub-Sub-Task) and it will never exceed that, it might be easier/more organized to have one table per level.
3. At an interface level, you can always apply levels/hierarchy, to show all related sub tasks for main tasks.
   
   

All of the above seems pretty aligned with @TheTimeSavingCo's answer above as well!

Mike, Consultant @ Automatic Nation

[Sachin_191]

Hey @Micouti 

You're on the right track with how you want to structure your Airtable base for program management. With Airtable, you can definitely achieve the level of detail you’re looking for, especially with multiple programs, phases, and tasks. Let’s focus on building a centralized structure to handle everything from top-level programs down to specific tasks and subtasks.

For your setup:

•  I'd recommend creating one base with multiple tables, each table corresponding to the major levels you're tracking (Programs, Phases, Projects, Tasks, and Subtasks). This way, you can link records and roll up data easily.
•  You’ll use linked records to connect programs to phases, phases to projects, and so on. This allows for clear relationships and visibility of dependencies.

Approach:

• Create a Programs Table where each program (e.g., Apple, Pear, Orange) is listed as a record. This will be your Level 1.
• Create a Phases Table to track the individual phases of each program (Level 2). Use linked records to associate these phases with their respective programs.
• Have a Projects/Tasks Table (Level 3/4) for all projects/tasks within each phase. You can link this table to phases and break down tasks, including custom fields for start dates, due dates, milestones, and status tracking.
• Subtasks can be handled in the same table as tasks or a separate Subtasks Table if you need extra depth, but Airtable’s task-level granularity can typically suffice.

Automation & Dashboards:

• Set up dashboards using Airtable’s blocks to visualize project timelines, stakeholder progress, and milestones. This will give you a clear snapshot of program health and outstanding tasks.
• Use Automations to trigger actions (e.g., linking form requests from external bases, updating task status, sending reminders).
•  Add a “Stakeholders” table to track stakeholder capacity and link it to your tasks table. You can then roll up the number of tasks assigned to each stakeholder across all programs.