How can I be sure that many to many relationships are created right when I click on one table and another table?

Sorry, but you lost me. I think I’m going to need examples because I can’t see how a recipe can be for many products. Why would you sell multiple products with the same recipe? The steps to reproduce a product might be similar from one to the next, but the recipe itself would be different (the ingredients for a chocolate cake will be different than for a yellow cake, even though the steps to make it are probably about the same)

@DaveMaxwell,

Yeah you are right. I wasn’t thinking this through like I should be. What I mean to say is a recipe has different ingredients for each product she sells. So the question is can I still have a recipe table linked to the product table and also have the same product table linked to the ingredient table? Would it make sense to do this?

@sealsd,

Please give me an example. I’m still lost on the difference between recipe and product.

To me, it’s semantics because one begets the other (she needs a recipe to make a product), but they’re not exclusive (the recipe changes for each product because the ingredients are not the same, so the steps are different. So, in my mind, you wouldn’t have six different products for one recipe. At the very least, the quantities of ingredients will change, so that means the recipe is different.

@DaveMaxwell,

A Recipe entity is needed to track the ingredients that go into the making of a product. A product can have many ingredients and an ingredient can be used in many products. A product is like cakes, pies, and pantries she sells after has been already made. The table that I have above is it right to connect it the way I did? Or should I do it another way?

The disconnect we’re having here is that the recipe IS the product - they are essentially one and the same.

You need a recipe to make a product, but a recipe will never make more than one product and one product will never have more than one recipe. Even a double batch of a recipe will create twice as much product. You change the recipe, you’ve now changed the product.

It’s a 1:1 ratio, so logically it doesn’t make sense to call it something else. But if it makes sense to you to have another table which is ALWAYS going to match up 1:1 with another table, then by all means go ahead and do it.

@DaveMaxwell,
What if I put in a loyalty program within my customers table? The loyalty program would be to reward customers for repeated purchases. So this is something that would go into the customers table? I guess so right?

Depends on how the loyaly program works.

If it’s going to give a 1% discount for people who ordered 10 different times, then nothing needs to be donebecause a count on the sales table for a particular customerId will give that.

If it’s going to be buy 10 cakes, get the 11th free, then you’ll need to add some data, which you can either keep on the customer table or keep a separate “tally” type table with the product type and the customer id, which you can then join and coujnt like in the previous, and trigger the discount that way. If you do this, though, you’ll need to account for those that like to save their discounts for later orders.

@DaveMaxwell,
How can I implement a loyalty program in my customers table? For example, if I want to give a 20% percent for any item they purchased for the fifth time. Can this be done in the customers table? How can I keep track of when the customer reached its fifth purchase? If the original price of an cookie is $7.00 and it would be on sale for 20% after the fifth purchase, then to find out how much the customer would have to pay I could just multiply original price by the sale discount rate. So then the sale discount would $ 1.40 but then I still have to subtract that from the original price. So the amount the customer would have to pay on the fifth purchase would be 5.60 dollars. How can incorporate these calculations in the customers table?

Something like

SELECT COUNT(li.ProductID)
  FROM Sales_Line_Item LI
  JOIN Sales S ON S,Sale_ID = LI.Sales_ID
 WHERE Customer_ID = 1234
   AND Product_ID = 234345

@DaveMaxwell,
Sorry if I keep asking you so many questions, but this should be the last question I ask. I think I understand now why the recipe table would be needed. You need a recipe because you need to know how much of an ingredient to put into the mix. For example, 5 gallons of milk and two pounds of chocolate powder, etc. I know that we discussed that a product was all the ingredients that went into making that specific product, but what we didn’t get clear was that a RECIPE IS NOT synonymous with the PRODUCT. A product is the final good that she sells to her customers, and a recipe is used to see how much OF an ingredient is needed to make the product. Now that we got that clear, is possible to add anything else to the RECIPE table?

I see where the confusion lies. In some iteration, you dropped the product_ingredient table.

In this version, you have it: How can I be sure that many to many relationships are created right when I click on one table and another table? - #13 by sealsd

In this version, you don’t: How can I be sure that many to many relationships are created right when I click on one table and another table? - #15 by sealsd

That table served the purpose you described, it listed the quantities of each ingredient you needed to make the product.

@DaveMaxwell,
So instead of calling it ProductIngredients. I can just rename it Recipes table. Right?

Call it what ever you want, but personally, I would find that confusing. A recipe != ingredients.

A recipe is a set of instructions on how to create something. An recipe references ingredients, but you have many ingredients to a recipe.

Personally, I would put a large text area in product called recipe which holds the instructions for making the product, and reinstitute the product_ingredients table. But if that is confusing for you to wrap your head around, then create a recipe table which holds the ingredients, then a table called recipe ingredients which holds all the ingredients and their quantities for a recipe. Just be aware that there is a 1:1 ratio between product and recipe.

@DaveMaxwell,

Here is another update on the model. Can you take another look at it?

@sealsd,

No. That’s completely off. You’re worse off than you were before…

  • You’ll never have a recipe for multiple products. If the recipe changes, the product changes. If you can give me an example, then I’ll change my thinking.
  • Where did your ingredients table go? It looks like you tried to merge it with recipe ingredients. You also lost your tie to OrderLineItems because of it.
  • You have many ingredients for one recipe, not many recipes for one ingredient

If it was me, I would go back to your model from post #21 earlier in this thread. It’s much closer to what you want.

If it was ME, I would do take that model and do the following:

  1. Remove recipe_ID and Ingredient_ID from Products and add a large text field called recipe. This field will hold the directions to make the product.
  2. Change the name of the recipe table to ProductIngredients and add Ingredient_ID and quantity.
  3. Add UnitOfMeasure to Ingredients
  4. All done.

But that’s me. You seem determined that you need a recipe table for whatever reason. So, take the model from post 21 and

  1. Remove recipe_ID and Ingredient_ID from Products. These are wrong no matter what
  2. Add UnitOfMeasure to Ingredients. This is still needed.
  3. Add Product_ID to Recipe. Change the name field to something like Directions so that it makes more sense.
  4. Add a new table called RecipeIngredients which has Recipe_ID, Ingredient_ID and quantity.
  5. Now you’re done (unless you change the parameters again…)

@DaveMaxwell,
I made a lot of changes. Can you look over my model one more time?

Except for Ingredient_ID still being on product, that’s what I suggested. You are showing a 1:many relationship between products and recipe (though your text says the right thing), but other than that it looks correct.

@DaveMaxwell,

Here is another updated version of my model. I added some fields to some of the tables as you can see. Let me know if you find anything wrong with it. When I tried to forward engineer my logical model into a physical database, it had some errors with the relationship from Products to Recipe. Products can have only one recipe, and a recipe is made for a specific type of product. Therefore, it should be a one to one relationship, right? And why is I am getting this error on the forward engineer process? Here are the new model pic and problem with forward engineer process below:


@sealsd,

So you’re creating yet ANOTHER table which sits between tables that has a 1:1 relationship? One word - WHY? You don’t need the recipes table as it could all be pulled into the product table, so you definitely do not need a look up table between the two tables which have a 1:1 relationship.

But to answer your question on why it’s failing, you’re referencing the product table in your recipe FK.

@DaveMaxwell,

How I am referencing the product table in my recipe FK?