Solved

Database Design for a Recipe

Posted on 2011-03-04
6
717 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now