Base design - prioritization of projects by changing objectives

[mirkolando]

Hello everyone,

I have a complex use case that I would like to implement in Airtable.

I have a table “Projects” where projects can be created via a form. Among other things, the form must specify how the project affects our strategic goals. So we have 6 fields with strategic objectives where the user can rate 1-10. “Strategic Objective A”, ‘Strategic Objective B’ etc.

Now these strategic objectives can change from year to year. However, I would like to know for each project on the basis of which objectives we defined the prioritization at the start of the project.

I would therefore create another table where the objectives are recorded annually and the prioritization of the objectives is recorded in another field.

Now I would like my interface (project overview) to display the objectives/priorities for each project that were defined at the start of the project, even if our general strategic objectives have changed in the meantime.

How would you set up the base and create the  links between the records?

I look forward to tips.

best regards

Mirko

[EddieROLM]

Hi Mirko,

Creating 3 tables for "Projects", "Rating", and "Strategic Objectives" should work for your use case. You'll need to rework the "Projects" table since having fields such as "Strategic Objective A", "Strategic Objective B"... is usually incorrect database design. The date on the "Strategic Objectives" table will allow you to add new strategic objectives every year and still keep the data distinct.

For your interface, once everything is linked up it should be simple to use lookup fields to get the data that you want.