Solved

Database Design for a Recipe

Posted on 2011-03-04
6
732 Views
Last Modified: 2012-05-11
Hi all,

I would like to design a database for Recipes. The table structure should be able to support information about the recipe such as its name, who wrote the recipe and when, as well as the recipe’s ingredients, ingredient quantities (and unit), and directions. How many tables I need for the Recipe database and what would be the relationships?

I have the following tables so far but I need a good database design for the Recipe's and the data types as well?


(1) Recipe Table

[RecipeID]  [RecipeName]  [RecipeAuthor]  [RecipeDirections]
  1               Happy Toast      Andrew             butter on bread, then toast bread, etc.



(2) Table Ingredients

[IngredientID]  [Name]                  
1                      butter  
2                      bread    



(3) Table RecipeIngredients

[RecipeID]  [IngredientID]      [IngredientDirections]
1                 1                            (toast needs butter)
1                 2                             (toast needs bread)


Could you tell me is there anything missing in the above database for Recipe's?

Thanks for your help. Looking forward to your reply.
0
Comment
Question by:Itudk_2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 35038199

ReceipeMaster
_______________
ReceipeID      INT IDENTITY
ReceipeName      VARCHAR
ReceipeDate      DATETIME
chefid            INT
IsDELTED      BIT
CreatedDate      DATETIME
CreatedBy      INT



Users
______
UserID            INT      IDENTITY
UserName      VARCHAR
Passwd            VARCHAR
IsActive      BIT


ChefMaster
____________
ChefID            INT      IDENTITY
CHEFNAME      VARCHAR
ISDELETED      IBIT


UOM
____
UOMID            INT IDENTITY
UnitofMeasure ( Grams,Litres,Kgs,meters....)      VARCHAR


ReciepeIngredients
__________________
ReciepteIngredientID      INT IDENTITY
REceipeID            INT
IngredientName            VARCHAR
UOMID                  INT
Quantity            INT
IsDeleted            BIT

ReceipeSteps
___________________
ReceipeStepID            INT IDENTITY
REceipeID            INT
StepIDSequence            INT
StepProcess            VARCHAR
IsDeleted            BIT
0
 

Author Comment

by:Itudk_2010
ID: 35038249
Hi sureshbabukrish,

Could you please explain your database design above? Also please explain why have you created the tables as follows?

ReceipeMaster
Users
ChefMaster
UOM

Looking forward to your reply.
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 500 total points
ID: 35038403
ReceipeMaster - used to store different types of receipes,
Users - who log into application and enter the data.
chefmaster - who are the owners of the receipes
UOM - unit of measurment - when ingredients are added , how much quantity is to be added and how do you measure it, usually in grams, milliliters.... like to save against each ingredient , this would be userful


Receipe Master                                    
ReceipeID      Name      Date      chefid      isdeleted      createddate      createdby
1      Sandwich      1/1/2011      1      0      1/1/2011      3
2      Ham Burger      1/1/2011      2      0      1/1/2011      3
                                    
                                    
Users                                    
Userid      username      passwd      isactive                  
1      scott      a      1                  
2      mike      b      1                  
3      ray      c      1                  
                                    
                                    
Chef master                                    
chefid      chefname      isdeleted                        
1      irene      0                        
2      walsh      0                        
                                    
UOM                                    
UOMID      UOM                              
1      Grams                              
2      Milliliters                              
3      Nos                              
4      Pounds                              
                                    
ReciepeIngredients      REceipeID      Ingredeint Name      UOMID      quantity      isdeleted      
ReciepeIngredientsID      1      Bread      4      0.5      0      
1      1      greens      1      100      0      
2                                    
                                    
ReceipeSteps                                    
ReceipeStepsID      ReceipeID      StepID      Process      IsDeleted            
1      1      1      wash the greens      0            
2      1      2      toast the bread      0            
3      1      3      apply cheese on bread      0            


last update here,

Make Quantity column as decimal in ReciepeIngredients Table
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 35038407
pls find sample data updated above
0
 

Author Comment

by:Itudk_2010
ID: 35038669
sureshbabukrish,

Thanks a lot for the explanation. Why the createdby column in the RecipeMaster table is an INT?
0
 

Author Comment

by:Itudk_2010
ID: 35038723
One more question on the same  RecipeMaster table? what is the difference between the ReceipeDate  and CreatedDate fields?

Thanks a lot for your help.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question