one Many-to-many growing out of control

[TinkerMan]

I have a problems which seems simple, but it's made me doubt everything I have learned about this; I suddenly find myself unable to grasp what I belive is a very small and easy solution, but I cannot!! (Either that or I have stumbled upon a real conseptual problem without realising it )

This is what I had working nicely

Supplier
supplyer_ID : ID and PK of a supplyer
more boring attributes

Ingredient
ingredient_ID: ID and PK of ingredient
Name
Supplyer_ID: FK to who the supplyer is

Recipe
Recipe_ID: PK and ID
Recipe_unit_weight: How big each unit should be
other stuff

When making a recipe it is obvious that there can be many ingredients and that an ingredient can be used in many recipes, thus it's a many-many. I therefore created a junction table:

Recipe_Ingredients
Recipe_Ingredient_ID: PK (I was not completely sure if this one would come in handy, but it has not so far caused any problems (that I can see)
Recipe_ID: FK to which recipe it is for
ingredient_ID: FK to which ingredient
Receipe_ingredient_amount: How much of the ingredient
Receipe_Ingredient_order: In which order to put into recipe

I had a nice form where the Recipe was the parent and the subform contained the ingredients. The subform was driven by a query that had the columns from the Recipe_Ingredient table plus the name of the supplier as well as the version of the recipe (as there might be many). Using a combo to get the name of the Ingredient and binding on the Ingredient_ID I didn't need to also bring in the name of the Ingedient in the query.

Now enter problem: For professional people this lacks one thing; Being able to use another Recipe as an ingredient. After a lot of thinking the solution I envisioned was;

Add a column in the Recipe to flag that is a base recipe (true/false). Then adding a new column to the Recipe_Ingredient table, namely Base_ID, which is the Recipe_ID for which recipe to use. As there is already a Recipe_ID referring to which the ingredient belongs, I needed a different name. Additionally I had to change the relationships in the subform query to be left-joins (always include all columns in the Recipe_Ingredient and join in whatever you can find in the other two.

This works for diplay purposes (when I hand edit the join table). The problems I get is when adding a new recipe as ingredient in the subform;

When adding a base (recipe) at the point of saving the record my Recipe table validation kicks in saying you need to have Recipe_unit_weight > 0!!! I am not planning on adding any recipes here. This does however not always happen, as another error also kicks in: Microsoft Jet database engine cannot find a record in the table 'Supplier' with the key matching field(s) ''. As it's a recipe there is no supplier and I'm assuming it is trying to find a supplier.

If I strip away the extra columns from the query powering the subquey (like supplier and base (recipe) version) it works like a charm. In effect it means that the query only consists of the junction table.

It seems that having a two-way junction table with left-join with both "parents" is causing problems. Am I doing something fundamentally wrong here or is it Access that don't get the picture (and how do I tell it).

Any hints or suggestions are welcome

PS: using A2K db's in 2003

Thx

 

[Banana]

This is a special case, to be sure. You're basically making a "group" (recipes) of entities (ingredients), then re-referencing it as an entity.

There's two ways about it.

One, you might want to consider calling it a self-join.

Alternatively, you can expand your validation rule to only execute if the checkbox you created is not checked.

As for the query doing the junction table, you really only need IDs. You don't need supplier or other misc *provided* you are not adding new supplier or modifying supplier or something like this. If all you need to do is to tell which recipe will be used as a base ingredient for another recipe, then you just need the recipe's key and store it as a foreign key in the junction table.

HTH.

 

[The_Doc_Man]

What you have discovered isn't new. In the manufacturing arena this is called a sub-assembly.

The trick is that a sub-assembly carries baggage whereas an ordinary part does not.

There are some tricks one can play. Banana's self-join idea isn't wrong but might be awkward. Another way you can do this is a dummy entry in your component table that gives a sub-assembly its own unique ID, which you then can use in some other assembly.

In your case, "first you make a roux" - as we Cajun cooks sometimes say...

OK, here is the issue. When you have sub-aggregates, you have two choices. First, you can make the sub-aggregate automatically expand into its components whenever you reach one. Second, treat the sub-aggregate as atomic for the purposes of the recipe. In other words, you ingredients can include, say, shrimp, noodles, a bechemel sauce (which is not elementary) or a roux (also not elementary), tomato paste, etc. etc. Then, flag them so that when you print out the recipe for your shrimp sauce, the complex components are always printed first so you know you have things to make before you can go on to the next step.

This is a conceptual problem in this sense: What are the components of the recipe? If the answer is that you add the flour, the trinity (bell pepper, onions, and celery, all diced), the oil or bacon grease (if you are like Emeril, who thinks that bacon grease is God's gift to chefs) at the same time as the other elements, then they belong to the other elements. But you don't. You use those elements first to make your roux. Then you add the roux as a single element later. Well, that is your real-world model, so you surely can just count the roux as a element later in your database.

This is one person's train of thought, often derailed, usually warped, often completely off track and way down a spur line. But think about it since you claimed you might have been having a concept issue.

On the other hand, here is some positive reinforcement. You correctly reasoned that many ingredients are common to many recipes, which is very clearly a many/many relationship. That type of thinking IS correct. It is just that sometimes Access (or any other relational system) can bog you down with subtleties that even trip up veterans.

 

[TinkerMan]

Thanx for the replies

Sorry my feedback was a bit late, but I was away for the weeked

Banana:
One, you might want to consider calling it a self-join.

This is a good suggestion, but it will limit me to only have one link (if I get your point) - so I can only use one other recipe as base, not many (yes, I know I making this complicated in addition )

Banana:
Alternatively, you can expand your validation rule to only execute if the checkbox you created is not checked.

For me this sounds like a workaround. The symptom of the problem is the validation rule being triggered, but the core of the problem is that access is trying to create a new recipe, which I'm not, as I only want to add a recipe as an ingredient to another recipe.

Banana:
As for the query doing the junction table, you really only need IDs. You don't need supplier or other misc *provided* you are not adding new supplier or modifying supplier or something like this. If all you need to do is to tell which recipe will be used as a base ingredient for another recipe, then you just need the recipe's key and store it as a foreign key in the junction table.

Yes, as I was saying, it all works fine if only the ID's is present in the sub-form query.

The_Doc_Man:
Another way you can do this is a dummy entry in your component table that gives a sub-assembly its own unique ID, which you then can use in some other assembly.

Yes, I was playing with this idea, but I decided to make it my plan B, as I thought plan A was cleaner and more elegant (also less prone to maintenance errors)

The_Doc_Man:
First, you can make the sub-aggregate automatically expand into its components whenever you reach one.

You are getting slightly ahead, as that’s part of the next crossroads, namely using this hierarchy for something useful. The additional complication is that the user wants to be able to get out one recipe for roux, which is the sum of all roux in any recipe multiplied by how many dishes ordered for each one. The way I’m planning to solve this is to use a temporary table and start on the bottom of the hierarchy and working my way upwards, summing the sums. I’m not going to attempt a one query solution

The_Doc_Man:
Second, treat the sub-aggregate as atomic for the purposes of the recipe.

Yes, that is what I am trying to achieve by using the recipe as the connection.

The_Doc_Man:
This is one person's train of thought, often derailed, usually warped, often completely off track and way down a spur line. But think about it since you claimed you might have been having a concept issue.

This is exactly why we solve problems; somebody else thinks in a different way Really appreciate it

To conclude:

For me it is a must to also display the other values, as there can be one ingredient with the same name, coming from multiple suppliers (like sugar).

So if I understand you both correctly, there is no way/trick that I can play which will prevent access from trying to update the superfluous display attributes?

If that is the case: What is the most efficient way of adding additional information (like supplier for ingredient or recipe version) without adding them to the sub-form query? Having the extra fields bound to a function, which does a sql to obtain the extra values? This approach seems a bit expensive, but that’s the only one I can think of…

 

[The_Doc_Man]

The trick with suppliers is that they exist on their own. (No, not being obscure - I have a point in mind.)

In general, when laying out relational databases, the very first thing you do is identify the elementary items. Items that exist regardless of whether any other item exists around it. Clearly, when you look at it this way, a supplier is an elementary entity in a database of this type.

Ingredients come in two parts. It is not so clear that an ingredient (unqualified) is elementary. This is because there is the basic definition of the ingredient and the way the supplier supplies it. Consider one of my favorite ingredients for certain South Louisiana recipies - crushed cayenne.

If you go to Zatarain's as the supplier, they sell it in jars in which the volume ranges from 1 pint (yeah, I know - not usually a dry measure) to 5 gallon jars. You can go to other suppliers - Baumer for example - and get similar sizes.

So to me, the ingredients table would be in two parts. The ingredient name and some descriptive materia, plus a junction table that shows how you buy it and from whom.

Followng that idea, ingredients are listed in recipes by quantities that (generally) have nothing to do with how you bought it. So that is ANOTHER junction table to link recipes to quantities of material. Now comes the big question - how to organize this stuff when there is at least one non-elementary item in the list.

I see this as occurring one of two ways.

1. Ignore the non-elementary components and only count the things you put together. If you have to make that yourself separately, so be it. But... you said you need the breakout - which leads to the other possible method...

2. Read up on UNION queries. Your recipe ingredients would be listed by the UNION of all junction entries for elementary ingredients UNION all ingredients called out in the junction entries of non-elementary ingredients.

This would get more complex if you ever have a recipe which involves a non-elementary ingredient which itself involves a non-elementary ingredient. At some point you would have to give up and say NO, we can't make that here...

Seriously, there is a third way but it is uglier than one cares to consider and if you are not happy with VBA, you will hate it.

Write VBA code to open some recordsets, one for input and one for output. Make entries in a temporary table to "flatten out" the ingredients list by first listing all elementary ingredients, then by remembering all composite ingredients, visiting THEIR entries, and add them to the table, then if you have another layer, go back until all elements have been visited. This is NOT trivial VBA because it involves recursion - not a simple topic for those not familiar with the concept. It is almost as bad a topic in VBA as self-joins are to SQL. But I'm being honest with you about difficulty factors.

 

[TinkerMan]

Thanks your input again, I really appreciate it

The trick with suppliers is that they exist on their own. (No, not being obscure - I have a point in mind.)

This is what I have concluded as well, so my suppliers already exist in a separate table.

If you go to Zatarain's as the supplier, they sell it in jars in which the volume ranges from 1 pint (yeah, I know - not usually a dry measure) to 5 gallon jars. You can go to other suppliers - Baumer for example - and get similar sizes.

So to me, the ingredients table would be in two parts. The ingredient name and some descriptive materia, plus a junction table that shows how you buy it and from whom.

I completely agree with your point of view as I try to generalize where possible, but I’m not quite sure this applies to my specific case. I’ll try to explain where I’m coming from; Firstly I actually (!!) have a simplifying element; all ingredients are measured in grams. Next the price differs from different suppliers. Also the quality differs from various vendors, which sometimes is used deliberately for cheaper/expensive products. For me this means that when making the recipe it is necessary to specify the exact one (from one specific vendor). In addition these different ingredients from different vendors can exist at the same time. Part of the Ingredient is also the vendors ID number and the price per kg. The name of the ingredients (from the suppliers side) are rarely called the same (which is why I have had to introduce an alias for using in recipe outputs as well as in the product declaration (which is the recipe ingredients in descending order). As a result of these factors, I have seen my current setup as the easiest considering my particular needs.

If I were to use the generalisation approach I suddenly would have to have a mapping table that says this product AB123 (sugar, white) from vendor A is the same as XY567 (refined sugar) from vendor X. For me (and not to mention the user) this will generate an additional step in the process.

2. Read up on UNION queries. Your recipe ingredients would be listed by the UNION of all junction entries for elementary ingredients UNION all ingredients called out in the junction entries of non-elementary ingredients.

This is exactly what I have done to create my dropdown that allows the user to select which ingredient or base (recipe) to use, together will all excessive attributes to allow user to select nicely. I have bound this combo to a temporary field in the junction table to prevent the selected value to be printed in all rows (which is what happens if it is unbound). Next in my After-Update event I inspect the users selection and insert the ID into the appropriate field. This works nicely, I even automatically get an update where it will put in the supplier/recipe version (if I use the sub-form query with the extra stuff). My problem arises when the whole row is attempted to be saved (as described earlier).

From what I seem to remember UNION queries are not updatable, so I cannot use this in the sub-form query.

Seriously, there is a third way but it is uglier than one cares to consider and if you are not happy with VBA, you will hate it.

Write VBA code to open some recordsets, one for input and one for output. Make entries in a temporary table to "flatten out" the ingredients list by first listing all elementary ingredients, then by remembering all composite ingredients, visiting THEIR entries, and add them to the table, then if you have another layer, go back until all elements have been visited. This is NOT trivial VBA because it involves recursion - not a simple topic for those not familiar with the concept. It is almost as bad a topic in VBA as self-joins are to SQL. But I'm being honest with you about difficulty factors.

He-he This is exactly the approach that I mentioned earlier, using a temp table to build my way upwards the hierarchy. My recursive cycle would be to find all recipes that are not used in any other recipes AND not already in the temp table. My exit criteria will be when I have climbed to the top and there is no recipes not in the temp table. I know this is will not be without hurdles, but it’s the only way I can see to handle multi-level inheritance in a relational database. I was thinking of implementing this in a loop instead of true recursion in an attempt to simplify things, but I’ll have see how it progresses.

This stuff is for my next task, but I’m more concerned for my current problem; Is it possible to tell access that only the ID’s needs to be updated and not the joined-in extra info (like supplier)?

Why does this work so nicely if I only have Ingredients? It is doing something very similar; The query behind the sub-form has the attributes from the junction table plus the joined in information like supplier. When I do edits/updates access is not trying to update the supplier info…why?

Cheers