December 4, 2010 at 5:19 pm
Hello,
I'm quite confused about how to implement a many-to-many relationship and my domain model. Let's say I'm creating a recipe box web application and I have three tables called Category (categoryID, categoryName), RecipeCategory (categoryID, recipeID), and Recipes (recipeID, recipeName, recipeDescription). If I were to present this information as an ASP.NET application and wanted to insert a new recipe and category, am I responsible for writing the logic to insert the information in the RecipeCategory table OR is it possible to set RecipeCategory.categoryID and RecipeCategory.recipeID automatically with default values based on newly inserted records in the related tables? If I'm responsible for writing the logic to insert values in RecipeCategory.categoryID and RecipeCategory.recipeID is that a trigger? What's the best way of implementing this?
Thank you,
Robert
December 5, 2010 at 5:23 am
robertlewis2001 (12/4/2010)
Hello,I'm quite confused about how to implement a many-to-many relationship and my domain model. Let's say I'm creating a recipe box web application and I have three tables called Category (categoryID, categoryName), RecipeCategory (categoryID, recipeID), and Recipes (recipeID, recipeName, recipeDescription). If I were to present this information as an ASP.NET application and wanted to insert a new recipe and category, am I responsible for writing the logic to insert the information in the RecipeCategory table OR is it possible to set RecipeCategory.categoryID and RecipeCategory.recipeID automatically with default values based on newly inserted records in the related tables? If I'm responsible for writing the logic to insert values in RecipeCategory.categoryID and RecipeCategory.recipeID is that a trigger? What's the best way of implementing this?
Thank you,
Robert
The best way to write is with direct TSQL code, not triggers. Taking your example, if a new category and a new recipe are inserted together, one could conclude that they might be related, and you could write a trigger that captures the values and inserts into the relationship table. But, first huge question, where does the trigger reside? You can't put it on both tables, because you can't insert a new PK into the RecipeCategory table with a NULL RecipeId or a NULL CategoryID. Instead, you'd pick one or the other table to manage the deal, ah, but then, how does that table tell what value was generated for the other table? You'll have to go and do a search, but you don't have decent criteria. Insert Date? Possibly, but couldn't someone else insert the value right after yours, before the insert to the second table completes, and now you're reading the wrong value.
Further, what happens when you just want to add a category, but not a recipe, or a recipe to an existing category? Or put a recipe in multiple categories? Nope, you need to take control of this process directly. Insert or get the category, insert X number of recipies and get their key values, insert the appropriate relationships, all through your code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply