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

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

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

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.