ERD for a recipe

Hi,
In my database design school project, we were given a bunch of cookie recipes.  I need an ERD.  I have several examples, but I cannot quite figure out where the "quantity" attribute belongs in my ERD.  One of the questions is List how much of each ingredient is in each recipe.  Here's my basic tables:

Cookie
ID
Name

Ingredient
ID
Name
Quantity????

Ingredient Used
PK, FK-from-Cookie-Table
PK, FK-from-Ingredient-Table
Quantity?????

Thanks
svillardiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
Just to push you in the "right" direction:

Suppose we have 2 cookies, one with 50ml milk, the other with 150ml milk.
Do you really need 2 "different" ingredients like "50ml milk" and "150ml milk" ?!?

Kind regards
Alex
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
svillardiAuthor Commented:
That's what I thought, and that's the example I used in my own head, but then I saw this:  http://creately.com/diagram/example/gsv8l5hs4/Recipe+Database, which doesn't include any attributes in the Ingredients List.

I would think it would have to be this way because it "looks right".

Ingredient List
PK,FK - Chocolate Chip Cookie ID
PK,FK - Milk ID
Quantity - 50  (but where I get stuck is -- are we referring to 50 cookies or 50 mls of milk?  both IDs are in this table)
Unit of Measure - ML

And then, I tried to figure it out the other way:  How would I write a query to double the recipe?  I am getting confused and stuck on how the PK/FK combines from each entity.
0
svillardiAuthor Commented:
Here's my ERD.  Am I on the right track?
cookies.jpg
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

svillardiAuthor Commented:
Any help on the above?
0
slightwv (䄆 Netminder) Commented:
Many Experts here don't do much on the site on weekends.

I assume you have already covered the "phrase" when it comes to normalization?

"The key, the whole key, and nothing but the key, so help me Codd."

That stated, do all your attributes relate to only the key?

Can I not have my sugar cookies in circles, squares, rectangles, etc...?
Maybe during Christmas and National Beer Drinking Day (Just found out that exists, Sept 28th).

Can you explain to us the difference between quantity and measurement?

What I suggest is create sticky notes about everything you know you need to record, then place them where you think they go.  Then keep repeating the "phrase" and move them around.

Eventually you won't be able to move them any more.
0
svillardiAuthor Commented:
I actually haven't heard that phrase.  I am struggling with the relationship to the key.

Answering your questions:

The shape and season are part of the recipe.  "Christmas cookies, Santa shaped (based on types - human, object, animal, supernatural) served during Christmas season."

The difference between quantity and measurement is 2 tablespoons vs 2 teaspoons.

It's the intersection entity which is confusing me, specifically the quantity of each ingredient for each recipe.  Writing the code for the intersection table, using the two foreign keys to create a composite primary key is another thing I can't figure out.

Thanks for getting back to me.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
ad Codd: http://en.wikipedia.org/wiki/Codd%27s_12_rules

It's the intersection entity which is confusing me, specifically the quantity of each ingredient for each recipe.

You will have to "connect" the recipes to the ingredients (many-to-many relation), thus you'll need a third (intersection) table.

using the two foreign keys to create a composite primary key is another thing I can't figure out.

You'll have to have those two keys, otherwise you'll not be able to "connect" these two tables. But you could use a "surrogate" key (just one ID column)...

http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
0
ste5anSenior DeveloperCommented:
Your ERD depends on the level of your current lessons. Especially as I would expect this kind of task a more sophisticated one. Cause you should model cookie recipes. This means using subclasses. Cause cookie recipes are a subclass of recipes - obvious, but important.

But you're already on the right track.

So you need at least

Recipes:
RecipeID, RecipeName
CookieRecipes:
RecipeID, CookieSeason, CookieShape
Ingredients:
IngredientID, IngredientName
MeasureUnits:
MeasureUnitID, MeasureUnitsName, MeasureUnit
RecipeIngredients:
RecipeID, IngredientID, MeasureUnitID, Amount

MeasureUnits data can look like
1, "Milliliter", "ml"
2, "Tablespoon (15 ml)", "T."
3, "Teaspoon (5 ml)", "t."

A further problem is the required detail level of your recipes. You maybe need to store additional general values in the Recipe table, e.g. like overall time and time to prepare.

You maybe also need the ordered list or procedural steps you need to make a cookie. This list can also be tricky. Cause it may require a relation to RecipeIngredients.

An additional notes section maybe also necessary.
0
svillardiAuthor Commented:
Well, @ste5an, that is quite a mouthful for a beginner like me.  

I ended up using 3 entities, Cookie, Ingredient, Ingredient list (intersection entity).

What got me was adding the Quantity to the intersection entity, as it didn't seem really pertinent to the Cookie entity, it seemed more appropriate to the Ingredient entity -- and none of the examples I found included any kind of additional columns in the intersection entity having to do with numbers, or quantities.

The attributes of the intersection entity also included Measurement (t, T, cup, etc).

When I tried to add a row from the command line Quantity changed from 1/2 cup (Qty, measurement), Oracle changed the 1/2 to .50 -- I emailed the teacher on that and she said to change it to a VARCHAR2 and include the Measurement, since there are no equations in this project.  

Well I don't wanna!!  :-)  Is there any simple way (at my beginners level) to make the number appear as a fraction?

The reason I don't want to change it is simple:  I want to include an extra query to double recipies!  :-)

I will be closing this question up shortly.

Thanks to all.
0
ste5anSenior DeveloperCommented:
Well, as I already said: it depends on the level of your current studies.

Whether a numeric value it is represented as 1/2 or .5 is meaning less for the database. It is a front-end task to use the correct output format.
I would consider using a fraction indicator in the MeasureUnits table. Then you know when a measure like spoon or cup is used with fractional values.

I emailed the teacher on that and she said to change it to a VARCHAR2 and include the Measurement, since there are no equations in this project.
What kind of school project are you doing? This violates the 1st normal form (1NF). Store atomic values. So you're right about that.
0
svillardiAuthor Commented:
I don't have a MeasureUnits table.  I have a Measurement/Unit column in the intersect table.  Can I still somehow represent .5 as 1/2?

This would be great!

She's just giving an easy way out for this 1st project.  It's just trying to do a logical to physical database.  Nothing "hard".  ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.