Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Design for a Recipe

Posted on 2011-03-04
6
Medium Priority
?
736 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 2000 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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