You SHOULD definitely (in my opinion) have an identity for the RECIPE to INGREDIENT.
What if you wanted to delete just one ingredient from a recipe? You could: DELETE FROM RECIPE_INGREDIENT WHERE RC_ID = #recipe# AND IN_ID = #ingredient# - which is fine an dandy, but if you are like me and would rather just have on field to reference on, you could: DELETE FROM RECIPE_INGREDIENT WHERE RI_ID = #refID#. Some many ways to do the same thing! -- Scott Van Vliet Senior Analyst SBC Services, Inc. ITO Enterprise Tools Office: 858.886.3878 Pager: 858.536.0070 Email: [EMAIL PROTECTED] -----Original Message----- From: Sam Roach [mailto:[EMAIL PROTECTED]] Sent: Friday, March 29, 2002 2:05 PM To: CF-Talk Subject: RE: CF/Database Help In this example is there any reason not to make RC_NAME and IG_NAME primary keys. I guess I just don't like auto numbers. So table 3 would look more like: RC_NAME IG_NAME ---------------------------------- Chocolate Cookies Flour Chocolate Cookies Sugar Chocolate Cookies Egg Chocolate Cookies Salt Chocolate Cookies Chocolate Chips Chocolate Brownies Flour Chocolate Brownies Sugar Chocolate Brownies Egg Chocolate Brownies Salt Chocolate Brownies Chocolate -----Original Message----- From: VAN VLIET, SCOTT E (SBCSI) [mailto:[EMAIL PROTECTED]] Sent: Friday, March 29, 2002 1:49 PM To: CF-Talk Subject: RE: CF/Database Help Depending on the depth of you database, the best thing to do would to be create a master RECIPE table, and a master INGREDIENT table. The, you could create an linkage table that would like n number of ingredients to a recipe. EXAMPLE: RECIPE ================================================== RC_ID RC_NAME RC_DESCRIPTION -------------------------------------------------- 10 Chocolate Cookies Yummy! 20 Chocolate Brownies More Yummy! INGREDIENT ================================================== IN_ID IG_NAME IN_DESCRIPTION -------------------------------------------------- 1 Flour White Stuff 2 Sugar Sweet Stuff 3 Egg Yolks & Stuff 4 Salt NaCl2 5 Chocolate Good Stuff 6 Chocolate Chips Little Stuff RECIPE_INGREDIENT ============================= RI_ID RC_ID IN_ID ----------------------------- 1 10 1 2 10 2 3 10 3 4 10 4 6 10 6 7 20 1 8 20 2 9 20 3 10 20 4 11 20 5 The table, RECIPE_INGREDIENT, would contain an identity for the relationship (RI_ID), a reference to the recipe (RC_ID) and a reference to the related ingredient (IN_ID). So, to find out what ingredients are used in Chocolate Brownies, you could: SELECT RI.IN_ID, IN.IN_NAME, IN.IN_DESCRIPTION FROM RECIPE_INGREDIENT RI JOIN INGREDIENT IN ON RI.IN_ID = IN.IN_ID WHERE RI.RC_ID = 20 You can go even farther and attach the recipe details to this query. But I hope this leads you in the right direction! -- Scott Van Vliet Senior Analyst SBC Services, Inc. ITO Enterprise Tools Office: 858.886.3878 Pager: 858.536.0070 Email: [EMAIL PROTECTED] -----Original Message----- From: Deborah Curley [mailto:[EMAIL PROTECTED]] Sent: Friday, March 29, 2002 1:31 PM To: CF-Talk Subject: CF/Database Help Hi, I have a friend whose a chef and has asked me build him a database of recipes and a web app he can use to search, enter new recipes, etc. What I'm trying to figure out is the best way to store the ingredients since a recipe can have anywhere from 5 to 20 ingredients? Any ideas on the best way to handle that? TIA, Deb _________________________________________________________________ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
