<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Timothy Claason
»
Denormalization Strategies
47 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Denormalization Strategies
Rate Topic
Display Mode
Topic Options
Author
Message
crussell-931424
crussell-931424
Posted Monday, March 15, 2010 7:35 AM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:52 AM
Points: 1,715,
Visits: 61
The approach we are taking is to normalize as much as possible. Then if it just doesn't work or can be made efficient, we denormalize.
Post #882925
James Stephens
James Stephens
Posted Monday, March 15, 2010 8:00 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 18, 2013 9:52 PM
Points: 28,
Visits: 169
We use a de-normalized data-mart--I may be using the term loosely but it's esssentially "a separate place for up-to-yesterday production data" where semi-trained users can put together queries on our core data without getting into trouble with join syntax and without us investing in a huge Crystal Reports development effort.
It makes a lot of sense for this because it's not the main production transactional db, we can get away with a read-optimized box with a few huge wide-flat tables rather than many tables with joins.
Disk space is cheap. Why make the same Y2k mistake and add unnecessary complexity to a data mart to save what amounts to in our case, about $100 worth of disk space.
90% of the most commonly asked reports are no longer dumped on the desk of our IT staff--a small investment in simple query syntax training for end users solved most of the issues.
Granted, we're small and simple but a similar approach this could be scaled.
In addition, we have some data-specific issue where this helps. For example, Customer 123 might be "Bobs Widgets" today, but later he changes it to "Bob & Sons Widgets". I understand about temporal tables but if you've ever seen what joining a temporal table does to an execution plan (not to mention 8 or 10 temporal tables) then that's something for which I'm more than willing to add a redundant field.
--Jim
Post #882954
Jaji03
Jaji03
Posted Monday, March 15, 2010 8:26 AM
Valued Member
Group: General Forum Members
Last Login: Monday, May 09, 2011 1:05 PM
Points: 69,
Visits: 104
Gilles Willard (3/15/2010)
It seems to me that what Timothy is proposing is simply adding a few FKs to his database design to cut down on the JOINs required to get at some data.
How is that denormalizing?
I had the same assumption...that is until he mentioned the fact that he decreased the number of tables. It would have been nice to have a diagram with the table design showing the suggested changes.
Post #882981
Paul White
Paul White
Posted Monday, March 15, 2010 8:27 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 10,990,
Visits: 10,542
Jim,
Yes. Data warehouses are a totally different kettle.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882982
Alvin Ramard
Alvin Ramard
Posted Monday, March 15, 2010 8:41 AM
Hall of Fame
Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031,
Visits: 7,383
Paul White (3/15/2010)
Jim,
Yes. Data warehouses are a totally different kettle.
It's normal for denormalization to be present in a data warehouse.
(Seriously, there was no pun intended.)
Alvin Ramard
Memphis PASS Chapter
All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #882998
Paul White
Paul White
Posted Monday, March 15, 2010 8:59 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 10,990,
Visits: 10,542
Alvin Ramard (3/15/2010)
(Seriously, there was no pun intended.)
Given your track record for bad puns, Alvin, I have my doubts
Benefit of the doubt.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #883019
Alvin Ramard
Alvin Ramard
Posted Monday, March 15, 2010 9:02 AM
Hall of Fame
Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031,
Visits: 7,383
Paul White (3/15/2010)
Alvin Ramard (3/15/2010)
(Seriously, there was no pun intended.)
Given your track record for bad puns, Alvin, I have my doubts
Benefit of the doubt.
If a pun had been intended, I would have included a smiley.
I can understand you comment. I do have a reputation for trying to add a bit of humor to many situations.
Alvin Ramard
Memphis PASS Chapter
All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #883022
Les Cardwell
Les Cardwell
Posted Monday, March 15, 2010 11:01 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:06 PM
Points: 81,
Visits: 255
In spite of the criticism, it was still a simple example of minimal denormalization to achieve an end result rather than a full-on explosion of wide rows to reduce the NF to 0
Interestingly enough, the biggest cost to the initial query, which probably exceeded benefits of denormalization, was using a 'function' in a WHERE predicate...
WHERE P_MS.DateReceived > getdate() - 365
...would have been better expressed declaring a scalar variable:
DECLARE @selectDate = getdate()-365
...
WHERE P_MS.DateReceived > @selectDate
...
...which would allow the optimizer to use an index on DateReceived.
Unfortunately, denormalization for immutable datasets as we've used it in the past just doesn't scale, especially on large datasets...not to mention the escalating complexity (and headaches) it entails. Ironically, not even for data-warehouses that go the same route (MOLAP) vs. a Multi-dimensional ROLAP Snowflake Schema. The stastical implications are the subject of current research, though it's proving a bit of a challenge to account for all the complexities it can entail (code proliferation, data-correctness, increased complexity of refactoring to accomodate changing business rules, data-explosion, etc.). The data-tsunami is upon us
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
Post #883154
baconm-1145631
baconm-1145631
Posted Monday, March 15, 2010 11:28 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 6:45 AM
Points: 4,
Visits: 39
Both of these examples depict DSS type operations. Rather then denormalize a live database I would prefer to created a data warehouse where denormalization is the norm. I believe you denormalize during load testing and then only IF you have a significant performance issue. Over time, a normalized database is easier to modify than a denormalized database.
Another alternative would be to use all natural keys and that way the UID of the parent table would be carried down to the UID of the children, grandchildren etc. Of course the big disadvantage to this approach is if you have many generations there would be more key columns than data columns in the lowest generation.
Post #883177
Jaji03
Jaji03
Posted Monday, March 15, 2010 11:30 AM
Valued Member
Group: General Forum Members
Last Login: Monday, May 09, 2011 1:05 PM
Points: 69,
Visits: 104
Les Cardwell (3/15/2010)
Interestingly enough, the biggest cost to the initial query, which probably exceeded benefits of denormalization, was using a 'function' in a WHERE predicate...
WHERE P_MS.DateReceived > getdate() - 365
...would have been better expressed declaring a scalar variable:
DECLARE @selectDate = getdate()-365
...
WHERE P_MS.DateReceived > @selectDate
...
...which would allow the optimizer to use an index on DateReceived.
Nicely said Grasshopper
I like the idea of denormalization, but many people look for these types of articles to re-establish their non-existing point of designing a sloppy, good for nothing database. They just totally ignore the last paragraph!
It usually takes alot of time and effort to denormalize a database. But shouldn't you FIRST NORMALIZE then DENORMALIZE if benefit can be measured???? Right???
Post #883179
« Prev Topic
|
Next Topic »
47 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
