I have been building a income and expense tracking base for myself that has evolved from a Google Sheet. Thus far I am loving the Airtable features and am anxiously awaiting a plotting solution. I have a question about implementing main categories and sub categories. For each expense I have defined main categories and sub categories. I have currently implemented this by creating separate tables within my base. One for main categories and one for sub categories and then linking to them within my main table.
My problem is that my design requires that each sub category belongs to only a single main category however I have to manage that manually as I do not know how to explicitly restrict that relationship. Thus far it works as I am the only one that uses the base and know which sub categories belong to which main category. I would however like to share the input responsibilities with others in my family and also make the base available as a more general solution. Additionally it would be good to have an explicit link that could be manipulated more easily in the future as the base evolves.
I have read through all of the Airtable guides that I can find. It seems like the solution might reside in the junction tables implementation, but I have as yet not been able to design a solution that would work. Any direction would be helpful or simply to know whether this is even possible to cleanly implement within the current Airtable structure. Years ago, before I even used a Google Sheet, I implemented a similar solution in Filemaker Pro but no longer remember exactly how it worked then and if such an implementation would even be possible in Airtable.