SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cocktail Recipe Database Schema - Tables Question - Normalization


Cocktail Recipe Database Schema - Tables Question - Normalization

Author
Message
MikeyLikesIt
MikeyLikesIt
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 19
I'm fairly new to database design. I have done a few in the past, however, I am trying to normalize my current database, and may be overthinking it, or not thinking enough.

In the current design I have an ingredients table which holds all ingredients. I am wondering if I should separate this table into many based upon ingredient type. For instance, "spriits" table, Juice table, etc... I am wondering if I leave all ingredients in one table. or will this make for inefficient queries in the future when table becomes large. I want to be able to search by, ingredients, or recipe name, and in future by shots/category etc..

I am assuming the more table the more efficient, and expandable a database for unknown future uses. Am I thinning correctly. Attached is my current EDR diagram.
[/img]
Attachments
recipes.png (74 views, 64.00 KB)
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3322 Visits: 4799
Given what I imagine is limited data in your ingredients table I would not break the table up for performance reasons. When I say limited I am assuming you have maybe thousands of records?

I wouldn't say that more tables is more efficient. I would say the correct number of tables is most efficient and from you diagram you look to be in good shape.

You could always categorize your ingredients (fruits, spirits, etc) either within the ingredients table or in a separate schema.
MikeyLikesIt
MikeyLikesIt
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 19
Thank you very much for the answer.

If I was to create another schema wouldn't that be separating the ingredients into their own tables? For instance, Vodka, Rum => 'Sprits', and orange, mango =>'Juices'. If i tried to categorize them into the ingredients table, I would end up with redundancies . I imagine I would need to create an ingredientType table then use foreign key in ingredients table to avoid any redundancies?

Funny thing when one starts over-thinking relations confusion starts to set in. I will leave the schema alone, and finish. I can alway add this later, but my thinking is it would be less of an 'update' pain if I did this now. However, I am unsure if having more tables, as you stated, would be more efficient, or better for possible future uses. As of now I can only think of two ways one would search for a cocktail; by recipe name, or by ingredients on-hand. I thought that some may actually try to find all 'Spirits' that contain 40% alcohol, or maybe organic juices, but this is probably way overthinking the logic.

Thank you again, I appreciate your perspective, and advice. :-)
-M-
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65012 Visits: 17979
Actually normalization (more tables) causes slower performance. That is the nature of the beast. However, in a relational database such as this you want to normalize. When designing your data don't think about how easy it is query and even worse is to change your schema based on performance. This is known as pre-optimization and it will cause you untold amounts of pain in the future.

I would suggest that you have an ingredient table and each ingredient can be of a type (juice, spirit, malt beverage, etc). Then create an IngredientType table to hold the string value.

I would avoid the use of sql reserved words like 'name'. Remember that there is nothing wrong with using some descriptive text with object and column names. It will make your life a lot easier in the long run if you use more descriptive names.

One other recommendation is to check your spelling. Category is misspelled. This may not seem like a big deal but common words spelled incorrectly in a database are not fun to work with. Measurement is also misspelled.

Your Category table has 2 columns I don't quite understand (martini and non-alchoholic). Those seem like rows instead of columns.

I am not quite sure what the quantity table is. It looks like a 1:1 with recipes?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
MikeyLikesIt
MikeyLikesIt
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 19
Thank you for your feedback. I am not done with the database, and the ERD has some tables that may be deleted, or are thoughts at this point.

I see the misspelled words (I'm horrible) I will take your advice and use more descriptive names. I was overthinking the idea. Measurements is going to hold "tablespoon" aka 'Tb', etc.. This is mostly for the form to submit keeping different spellings, or abbreviations out of the recipes. Still working on overall layout.

I have much work to do and will take your suggestions (recommendations) to heart. The category table is just a thought, I may use may not. I want to possible organize drinks by category for simplicity because there are many types of martini's, amount others. Was just thinking if the three tables Ingredients, Recipes, and drinks were 3NF. I have a horrible habit of overanalyzing.

I will use the links you provided to further develop my understanding.

Thank you again.:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222486 Visits: 42003
Sean Lange (7/29/2014)
Actually normalization (more tables) causes slower performance.


"It Depends". ;-)

If you have de-normalized table that looks like a spreadsheet with date columns in it, queries can be both difficult to write and very slow in performance. "Normalizing" such a table to be more like an EAV table will allow for proper indexing, easy non-reporting style lookups-aggregations-etc, and still allow for some very high performance CrossTabs and Pivots for reporting purposes while also making it easier to create "moving window" reports.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26471 Visits: 12506
Sean Lange (7/29/2014)
Actually normalization (more tables) causes slower performance. That is the nature of the beast. However, in a relational database such as this you want to normalize. When designing your data don't think about how easy it is query and even worse is to change your schema based on performance. This is known as pre-optimization and it will cause you untold amounts of pain in the future.

My experience with reasonably straightforward databases is that nomalisation to 3NF improves performance because it reduces the data size significantly. Of course if you end up needing to join 5o tables the performance will be awful, but tends to happen when you normalise to Date's 6NF and not when you normalise to 3NF.

I would suggest that you have an ingredient table and each ingredient can be of a type (juice, spirit, malt beverage, etc). Then create an IngredientType table to hold the string value.

That looks like the right way to do it to me.

I would avoid the use of sql reserved words like 'name'. Remember that there is nothing wrong with using some descriptive text with object and column names. It will make your life a lot easier in the long run if you use more descriptive names.

One other recommendation is to check your spelling. Category is misspelled. This may not seem like a big deal but common words spelled incorrectly in a database are not fun to work with. Measurement is also misspelled.

I agree with those comments.

Your Category table has 2 columns I don't quite understand (martini and non-alchoholic). Those seem like rows instead of columns.

I am not quite sure what the quantity table is. It looks like a 1:1 with recipes?

I don't understand those bits either.

Tom

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101151 Visits: 33014
Sean Lange (7/29/2014)
Actually normalization (more tables) causes slower performance.


I want to let this go, but I just can't.

No. Not true.

Everything, everything, within a structured storage engine comes with trade-offs and costs. Nothing is free. TANSTAAFL always applies. But a properly normalized structure with appropriate indexes and enforced referential constraints generally outperformers a flat, denormalized structure. This is because the relational engine is optimized around supporting the relational storage and retrieval of data. Can we identify situations where this is not true? Absolutely. But by and large the normalized structure is superior for the types of applications and needs that structured, relational storage is built for.

We can go down the road of talking about id/value pairs of unstructured storage for data collection. But, they don't support querying the way relational storage does. So, different requirements, different structures, different performance paradigms. So, if we're talking about different applications and different needs, then, yes, relational storage falls off a cliff and normalization hurts performance. But then, we shouldn't be using a referential engine for the storage anyway.

Sorry, so sorry, just saw this statement one too many times this month.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65012 Visits: 17979
Grant Fritchey (7/30/2014)
Sean Lange (7/29/2014)
Actually normalization (more tables) causes slower performance.


I want to let this go, but I just can't.

No. Not true.

Everything, everything, within a structured storage engine comes with trade-offs and costs. Nothing is free. TANSTAAFL always applies. But a properly normalized structure with appropriate indexes and enforced referential constraints generally outperformers a flat, denormalized structure. This is because the relational engine is optimized around supporting the relational storage and retrieval of data. Can we identify situations where this is not true? Absolutely. But by and large the normalized structure is superior for the types of applications and needs that structured, relational storage is built for.

We can go down the road of talking about id/value pairs of unstructured storage for data collection. But, they don't support querying the way relational storage does. So, different requirements, different structures, different performance paradigms. So, if we're talking about different applications and different needs, then, yes, relational storage falls off a cliff and normalization hurts performance. But then, we shouldn't be using a referential engine for the storage anyway.

Sorry, so sorry, just saw this statement one too many times this month.


Oh how I regret not making my original comment less absolute. w00t

I would never suggest we denormalize our structures in most normal situations for many reasons.

And certainly no need to apologize for calling me to task when I post something so blatantly incorrect.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101151 Visits: 33014
Sean Lange (7/30/2014)
[quote]Grant Fritchey (7/30/2014)

Oh how I regret not making my original comment less absolute. w00t

I would never suggest we denormalize our structures in most normal situations for many reasons.

And certainly no need to apologize for calling me to task when I post something so blatantly incorrect.


Ha! Again, apologies. I know I went off on a rant and that was wrong. Maybe I need to step away from the forums for a little while.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search