Normalizing-Denormalized Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohit K. Gupta » Normalizing-Denormalized Tables

48 posts, Page 1 of 5

1 2 3 4 5 »»»

Normalizing-Denormalized Tables

Rate Topic

Display Mode

Topic Options

Author

Message

Mohit K. Gupta

Posted Sunday, November 09, 2008 1:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

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

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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

How to ask for help .. Read Best Practices here.

Post #599528

Micke Schonning

Posted Sunday, November 09, 2008 12:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:57 PM
Points: 5, Visits: 86

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

Post #599590

Jeff Moden

Posted Sunday, November 09, 2008 9:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #599635

Mohit K. Gupta

Posted Sunday, November 09, 2008 11:30 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

Micke Schonning (11/9/2008)

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

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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

How to ask for help .. Read Best Practices here.

Post #599653

Mohit K. Gupta

Posted Sunday, November 09, 2008 11:34 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

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.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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

How to ask for help .. Read Best Practices here.

Post #599654

daniel-851844

Posted Monday, November 10, 2008 2:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 5:29 AM
Points: 46, Visits: 64

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.

Post #599692

henrik staun poulsen

Posted Monday, November 10, 2008 2:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 1,131, Visits: 855

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

Post #599695

Robert-378556

Posted Monday, November 10, 2008 4:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,078, Visits: 848

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

Post #599724

Anipaul

Posted Monday, November 10, 2008 4:37 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 4,787, Visits: 1,336

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

Post #599741

Derek Dongray

Posted Monday, November 10, 2008 4:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

Post #599743

« Prev Topic | Next Topic »

48 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions