I have a table called FoodMaker and another table called Settings. Every FoodMaker has multiple Settings. I also need a 3rd table to record what has been made, when and with what Settings. It might have these fields: id, food_made, date_time, settings_used.
This is pretty straightforward since I can put IDs' of FoodMaker and Settings in the 3rd table to reference them. But the problem is, the Settings may change later but I want the Settings which the food has been made with and not the updated one. So I need to take a snapshot of Settings each time I'm INSERTING into 3rd table.
One easy way could be just to add all of the Settings fields to the 3rd table on each INSERT. But is it not redundant and ugly? Is there a better solution?
I believe the problem is in your definition of the entity "settings".
If settings is a recipe, like "I made the hamburger id XX using Bob's recipe". If Bob's recipe is a record in a table, or the "settings" of the hamburger, then you should not just update it without keeping some sort of version control about it.
I believe the correct way to model this problem is to relate what "has been made" with a version of Bob's recipe, or "settings".
Keep everything that can change in a table related to the original Bob's recipe. There is more than one way to do this. Here's an example:
Hamburger A: id 1, settings 5, date 11/18/2014;
Settings 5: recipe Bob, delta-coffee +5ml, delta-sugar -1g; # (metrical units ml and g)
Recipe Bob: cream: 10 ml, coffee 10ml, whiskey 100ml; sugar 3g; (metrical units ml and g)