Advise needed - database structure

Questions
1

Hi all,

We are trying to move excel budgets to an online tracker app and I am thinking what is the best way to structure the database.

We have a few projects that have maintenance budgets for a number of years. In other words, we have the following structure:

  • Projects : ie Building 1, Building 2 etc
    • Each project then has a budget of repairs for each year for the future (ie 2023 ->X amount for Project 1, 2023->Y amount for Project 2
      -Each project track actual expenditure and compare it against the set budgets.

Is there a better way to structure the database and associate Projects->Years->Budgets & Actuals apart from having the following structure:

  • Datatype : Project (list of projects)
  • Datatype : Budget with fields Project (to link to above), year and value
  • Datatype : Actual with fields Project (to link to above), year and value to compare against budget.

The reason that I think the above might not work is we have around 1000 projects and each project has many budget periods so the database would grow pretty fast.

Any advise would be appreciated.

Thanks

2

There’s only 1 component and that’s your project. Each project has it’s own budget, so I wouldn’t make a list of projects. Especially if you have 1,000.

So, let each new project with a new budget be a new entry

Data Type: Project Estimate
Field: Name {text}
Field: Budget {number}
Field: Year {date or number}

Data Type: Project Actual
Field: Name
Field: Budget
Field: Year

Maybe?

3

Hmmm, maybe another perspective;

Project

  • Name [text]
    // any other fields specific to the project like address, images, key contacts etc

Budget

  • Project [project]
  • Year [number] // can also store as date if it makes more sense for your use case, for example financial years vs calendar years
  • Amount [number]

Expense

  • Budget [budget]
  • Amount [number]
    // any other fields specific to the expense; vendor, invoice number etc.

Create budgets for each year that the project has a budget. Create expenses for each expense and then you can compare them against the budget that they are assigned to.

4

Hi @andromeda,

Check out this video, I have learned a lot by watching Matt videos about database structure, highly recommended.