Solved

Database Design for a Recipe

Posted on 2011-03-04
6
727 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display info from DB to Label in asp.net 7 40
efficient backup report for SQL Server 13 76
comparing two rows 10 35
Question about Common Table Expressions 3 38
Creating and Managing Databases with phpMyAdmin in cPanel.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

734 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