Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cocktail Recipe Database Schema - Tables Question - Normalization Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 2:03 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:46 PM
Points: 3, 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]


  Post Attachments 
recipes.png (29 views, 64.35 KB)
Post #1597487
Posted Tuesday, July 29, 2014 2:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:01 PM
Points: 653, Visits: 3,867
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.



Post #1597493
Posted Tuesday, July 29, 2014 3:00 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:46 PM
Points: 3, 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-
Post #1597503
Posted Tuesday, July 29, 2014 3:12 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1597508
Posted Tuesday, July 29, 2014 5:49 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:46 PM
Points: 3, 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.
Post #1597530
Posted Tuesday, July 29, 2014 5:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 35,216, Visits: 31,670
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597531
Posted Tuesday, July 29, 2014 7:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 7,696, Visits: 9,424
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
Post #1597544
Posted Wednesday, July 30, 2014 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 13,752, Visits: 28,148
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1597755
Posted Wednesday, July 30, 2014 8:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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.

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 Moden's 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)
Post #1597767
Posted Wednesday, July 30, 2014 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 13,752, Visits: 28,148
Sean Lange (7/30/2014)
[quote]Grant Fritchey (7/30/2014)

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

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1597774
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse