Normalizing-Denormalized Tables

  • Comments posted to this topic are about the item Normalizing-Denormalized Tables

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • The function CombineGroupNames() can be optimzed using this concat-logic:

    DECLARE @Groups VarChar(MAX)

    SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)

    FROM Resource R

    INNER JOIN ResourceSecurityGroup RG

    ON R.ResourceID = RG.ResourceID

    INNER JOIN SecurityGroup G

    ON G.GroupID = RG.GroupID

    WHERE R.ResourceID = @ResourceID

    RETURN @Groups

    /Mike

  • I'm all for normalizing data... but if no one is going to use the normalized form, then you shouldn't bother. In this case, it actually provides a negative ROI as well as a performance problem because the solution uses RBAR on steroids and the fact that no one has the nads to smack the vendor.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Micke Schonning (11/9/2008)


    The function CombineGroupNames() can be optimzed using this concat-logic:

    DECLARE @Groups VarChar(MAX)

    SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)

    FROM Resource R

    INNER JOIN ResourceSecurityGroup RG

    ON R.ResourceID = RG.ResourceID

    INNER JOIN SecurityGroup G

    ON G.GroupID = RG.GroupID

    WHERE R.ResourceID = @ResourceID

    RETURN @Groups

    /Mike

    Thank Mike. I am still new to articles; I don't think I can make adjustment. But this is very helpful hint thanks :).

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Hi Jeff,

    Thank-you for the comments, I didn't think RBAR would be big issue in my case because the table had limited number of rows and wasn't going to grow. But I forgot to take into account when posting article here that other people reading this article it can be an issue. I am always telling the developers to stop working on the data in SQL Server on row-per-row basis; and I am doing it here myself. Thanks again for the *swift kick*; I will do better next time.

    And your comment on Vendor, I have fought more then one battle against vendors. And lost them all, because I always get brought in at the last moment of when a system is going live; or when its been down for a while. So when working on it I find something that should be changed or altered. I get two responses, 1) it costs money to fix it and 2) we don't have time for it right now; we'll look at it later.

    - Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • I also wonder about the praticality of this. Surely it would only be important to normalize a 3rd parties table where the normalised tables would fully utilise CRUD (Create, read, update, & delete). Since the only "benefit" here is the read aspect I would think it would be better off untouched.

  • Hi Mihit,

    Thank you for a nice example on how to get yourself away from bad table design, when retaining backwards compatibility.

    What you could also have shown was the use of Insert Instead-of triggers, so that the view was updateable. Then you would have had 100% backwards compability.

    I also wondered over your use of a cursor, but I thought that was because the set-based solution cannot garantee an ordered result.

    Best regards,

    Henrik Staun Poulsen

    Denmark

  • Nice article. There are some mistakes in examples which indicates lack of attention to details, but the intent for sharing ideas is most important.

    You can expand on this. What you got is backward compatibility for select queries. Additionally, you can create "instead of" triggers on views, so you get update compatibility too. Idea for part II of the article. 🙂

  • A good article to rectify the mess of creating unnormalized tables. After going thru this article people should realize the importance of normalization

  • The function CombineGroupNames can be dispensed with entirely by defining the view thus:

    CREATE View [dbo].[ApplicationResources]

    AS

    SELECT R.[ResourceName]

    ,R.[IsEnabled]

    ,replace(replace(replace( (

    SELECT '['+sg.GroupName +']'

    FROM ResourceSecurityGroup rsg

    JOIN SecurityGroup sg ON rsg.GroupID=sg.GroupID

    WHERE rsg.ResourceID=R.ResourceID

    ORDER BY GroupName

    FOR XML PATH('') ), '][', '|'), '[', ''), ']', '') as 'Groups'

    FROM dbo.Resource R

    Of course, with such as small dataset it's difficult to determine what's most efficient, but getting rid of the cursor is always a good idea.

    [Edit] BTW, I agree with the comments that 'in the real world' it would probably not be worthwhile to do this on such a small dataset, but it does serve as a good example of a workaround to a problem where the vendor won't fix what may be an obvious problem. In fact, I've used similar workarounds (creating views for old tables) when a large application needs various tables restructuring but resource meant that all sections of the application couldn't be changed to use the new structure in one go.

    Derek

  • If the tables were not being updated to often wouldn't an indexed view allow you to not have the processing of the view unless the the data changes?

  • Thanks for the well written, clear article. I like to see new authors give it a go. Consider updating this article to remove the RBAR, as suggested by the discussion. I hope to read your followup article soon, entitled "Normalizing-Denormalized tables Part II: Insert Instead-Of Triggers".

    Paul DB

  • I want to set the record straight. I just read Mohit Gupta's piece on normalizing a not normalized table (Nov 10, '08). I accidently rated it with one star when I really wanted to rate it 5 stars. I hope I have not screwed up the average too badly. Kayuca.

  • I agree with using the SELECT ... FOR XML PATH('') to get a set based operation. Not only does it remove the cursor, but it greatly simplifies the code.

    What I would recommend doing for it though is to instead of using all of the replace statements, is to:

    declare @Groups varchar(max)

    select @Groups = (

    select '|' + G.GroupName

    FROM Resource R

    INNER JOIN ResourceSecurityGroup RG ON R.ResourceID = RG.ResourceID

    INNER JOIN SecurityGroup G ON G.GroupID = RG.GroupID

    WHERE R.ResourceID = @ResourceID

    FOR XML PATH(''))

    set @Groups = substring(@Groups, 2, 1024)

    return @Groups

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank-you everyone on the feed back :).

    I been working on the 2nd part of this article. Just been busy with studies for MCITP and my masters. But I plan to get that out soon; and I really appreciate the comments. I will address all the comments here.

    When I did this solution we didn't have too many updates. We had a few updates where we had to add groups for some resources and remove it for others and our group name followed fully-quantified active-directory name (OU=Domain,OU=Dept,OU=Grp1|OU=Domain,OU=Dept,OU=Grp2|etc...) so if you miss a comma or pipe ("|") it caused issues. So even though I had to take a bit of a performance hit I figured it was worth the headache we kept running into. But I must admin I did not consider XPATH or String concatenation for the function as a solution. Thanks again for all the tips.

    I didn't create index for the view to improve the peformance further, I can also look at doing that in part-2.

    I'll try talking to SSC editors to see maybe I can revise my original article. So to eliminate the RBAR issues; and fix any other problems that have been identified.

    Again, thanks a lot folks.

    - Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

Viewing 15 posts - 1 through 15 (of 62 total)

You must be logged in to reply to this topic. Login to reply