database design – ERD for yearly budget ceiling advice

Honestly this looks like a classic hierarchical problem that can usually be solved with a single table of each unique item and a separate self-referencing table that holds each parent-child relationship. This is a common problem in the manufacturing industry but evidently applicable even in budgeting use cases.

So you have a series of items that are all similar in object definition but just differ in hierarchy, a Program, Activity, Sub-Activity. Rather than defining separate tables for each, you should have a single table perhaps called BudgetItems, that would store the unique list of each of those objects. That table can have a separate field that identifies the type of object it is, BudgetItemType. (It would have it’s Id and Name columns as well.)

Then you should have a table that stores every possible parent-child relationship between all BudgetItems, perhaps called BudgetItemHierarchy (or whatever better name you find appropriate). It would have two columns, BudgetItemParentId, and BudgetItemChildId, both foreign key references to your BudgetItems table. There would also be a BudgetAmount column that corresponds to the amount budgeted for the BudgetItemChildId.

Having these two tables in place will allow you store any number of budget objects and budget object types, without having a number or redundant tables. Then in most modern relational database management systems, you can use recursion, such as a recursive CTE to calculate any list of BudgetItems for any subset or full set of their hierarchy with all their individual BudgetAmounts at each level, and even the rolled up sum across any number or all levels.