In this video, you have the student_class relation to allow multiple students to attend multiple classes and classes to have multiple students. I am trying to do something similar, but I want an additional column in that table (if possible). In my case I have batches of products that have multiple ingredients and sometimes those batches can have more than one instance of a particular ingredient. Ie, suppose part way through the batch we run out of a particular instance of an ingredient with LOT number 01 so to finish the batch, we move onto LOT number 02 of that ingredient. I would like to record how many of that batch of products are made with ingredient 01 and how many are with 02. So what I tried to do was create a table product_ingredient with a product column, an ingredient column, and a qty column, but now I cannot come up with a good way to build forms to list and add to that table smoothly.
Each batch always has a set amount of employees who start and finish the batch together and when I created my product_employee table with only two columns one for the employee and one for the product, I am able to get what I want, and I am making good progress building forms in that case, but I am stumbling trying to come up with a solution to the three column table. Is this just bad database design?
Should I create a third table product_ingredient_qty where with two columns product_ingredient and qty? That is doable, but I am going to end up with a large number of tables, or is there a solution within REI3 to create better forms to accommodate this?
