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
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
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,
BudgetItemChildId, both foreign key references to your
BudgetItems table. There would also be a
BudgetAmount column that corresponds to the amount budgeted for the
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.