I have two tables, tblRecipes and tblChemicals, that are linked in a many to many relationship using a junction table tblRecipesChemicalsLink. I have a bunch of variables for calculations that differ between the same ingredient used in a different recipe. There are also a differing number of those variables between the same ingredients for different recipes:
I want the database to be flexible in the number of variables that can be associated with the unique combination of recipe and chemical, so I wanted to create a 1-m relationship from the junction table tblRecipesChemicalsLink to a table holding my variables with fields like Value,Name,Description,ID etc. I have not figured out a way to do this succesfully. The junction table in access only lists the individual keys for tblRecipes and tblChemicals, but I would need to link the variable table to the unique combination of those keys. Adding a new ID field to the junction table and adding an ID field as primary key for the Variable table and then linking those only allowed for a many-one relationship between junction and variable table. I would need it to be the other way around. Is there some way to do this in Access? Do I have to somehow write a new custom key and construct it from the primary key values of the tables that are linked by the junction table manually?
I am using ms Access. I am looking for a table with a different coefficient in every ROW linked to the junction table, not different COLUMNS for every class (like in inheritance). The coefficients cannot be assigned to meaningful subtypes in my case.
Do some research on Ternary Relationships. These are relationships of degree three.
Your existing junction table implements a M-M binary relationship. You are going to want an M-M-M relationship. This means that your junction table will consist of not two but three foreign keys. The first two will identify a recipe and a chemical, as they do now. Your new table, (let's call it coefficients) has an ID field, as you have pointed out in your question. A foreign key that references coefficient.id is what you need to add to the junction table.
This means that every Recipe-Chemical pair would need one or more rows in the junction table, one for each coefficient that participates in the recipe for the chemical.
There are more design issues. Your coefficient table is going to have to handle some abstract form of typing unless every coefficient can be specified as a floating point number.
This is really one step towards a star schema design. I have never attempted a start schema design in MS Access, and I don't know what pitfalls you are getting near.