Lets say we have an entity recipe (id, version, other_stuff) with a child entity ingredient (recipe_id, recipe_version, other_stuff). Any recipe can have multiple ingredients, and ingredients can only have one recipe.
We want to implement SCD Type 4 for recipes (thus the version columns above), and by extension the ingredients need to be part of the versioning system.
If we make a recipe_history (id, version, other_stuff) table that stores the old versions of the recipes, and don't make one for the ingredients, then the ingredients records have foreign keys to two tables - recipe and recipe_history.
But to my knowledge that is not possible in SQL. Is the only alternative to make a new table ingredient_history, and copy and all the ingredients for each old recipe version to this table?
One obvious downside to this is that you end up making copies of ingredients that may not have changed from version to version.
Is there a better way to handle this?