I’m working on a schema for a cocktail recipe app. I have ingredients split into three tables based on taxonomy: Broad Spirit Types (eg Gin), Narrow Spirit Types (eg London Dry Gin), and Bottles (eg Beefeater). In the wild, recipes can call for any type of those three ingredient types, and I’d like for my schema to accommodate that.
Right now, my workaround is for each recipe line item there are three separate columns (Broad, Narrow, Bottle). But that means I don’t have a single “ingredients” record that collects all the ingredients for each recipe (for example, a recipe that calls for gin, Campari, and sweet vermouth would have its ingredients in the Broad, Bottle, and Narrow ingredient columns, respectively).
So I’m looking for a way to have one ingredient column where I can choose from any of the three tables, OR a field that can essentially gather up the items from my 3 workaround columns and group them into one ingredient column.
And it’s important that the final ingredient column maintain the links to the tables. I imagine I could achieve a rudimentary version of this with formulas, but as far as I can tell, that’d convert them to text-only and destroy the links to the tables. Not ideal.