The Pain of Poor Database Design

Editor's Note: SQL Server Magazine welcomes feedback about the magazine. Send your comments to [email protected]. Please include your full name, email address, and daytime phone number with your letter. We edit all letters and replies for style, length, and clarity.

The Pain of Poor Database Design

Jeffrey Bane's article "The First Abnormal Form" (August 2001) is what I consider a "carry around" article. I'll carry it around as proof that poor database design is usually the root of performance problems. As an independent consultant working mostly with SQL Server and Oracle database design, maintenance, and redesign, I found the article invaluable.

In the SQL Server environment, I'm running into a recurring problem. I'm finding small to midsized companies that have given the database-design job to the Visual Basic (VB) or C++ programmer. SQL Server is very easy to install out of the box (as opposed to Oracle, which someone without experience simply can't install). So, companies think that if SQL Server is easy to install, it must be easy to design. Why hire someone new when your VB person can do the job, right? And of course, the VB person is willing to learn a new skill while continuing to perform his or her main duties. But the results can be disastrous. Ever seen a 300-table database in which all the data types are varchar(255)? Dates, numbers, dollar amounts—you name it, it's a varchar(255). Not a pretty sight.

—J. Jeffrey Schwartz
[email protected]

Thanks for your comments, and let me say that I feel your pain. In my opinion, SQL Server's ease of installation and administration both helps and hurts the product. Sometimes I wish Microsoft would make SQL Server more difficult to install and administer (like Oracle) so that people would understand that database development and administration is a serious task. But on most occasions, I find myself thanking Bill Gates that SQL Server has simplified most operations so that we can spend our time addressing the more important and time-consuming database tasks, such as proper database design. As to your main point, I don't know how we can spread the word that database design is actually a genuine skill that requires a qualified, experienced professional. I think your letter is a good start!

—Jeffrey Bane

I enjoyed Jeffrey Bane's article "The First Abnormal Form" (August 2001). Too many times in the past, I've been stuck supporting someone else's table design only to find problems just like Bane described. I found the article to be well written and thought out until the section about "Abbey Someone." Table AB2 suffers the exact problems (except for the data-retrieval issue) that Bane was trying to eliminate. I'm not sure what the article's stored procedure was trying to accomplish, but a much simpler way exists to populate the drop-down list. I would have created the SELECT statement to return one record, then used the Split() function and a For..Next loop to mimic the Do While in the article's Active Server Pages (ASP) page, as the code example in Listing 1 shows. No stored procedure and a simpler ASP page would have done the trick.

—John Hembree
[email protected]

Thanks for your input. I don't think I've ever received so much feedback about an article before; it seems that quite a few of us are having to deal with tables in the first abnormal form. You make an excellent point that the Split() function, as your example shows, would work nicely. In this article, however, I wanted to stress the complexity involved when using T-SQL to deal with comma-separated strings. Most DBAs have enough to worry about without having to master VBScript. Most traditional languages do have a richer set of commands for dealing with string operations than T-SQL does. But no matter what language you use, if your database isn't properly normalized, you're going to be doing more processing than necessary.

—Jeffrey Bane

Where's the Payback in Learning New Syntax?

I found Michael Otey's Editorial: "VB 6.0 vs.VB.NET" (July 2001), which talked about the pros and cons of Visual Basic.NET (VB.NET) changes, amusing. Funny how we each have a different way we think a particular detail should work. Otey said he appreciated that True should be 1, not -1, and that a function should return its payload with a return statement rather than assigning the value to the name of the function. I really don't care which way Microsoft implements the functions. What I care about is that these changes mean learning a new syntax with no discernible benefit.

I'm guessing that many of the VB changes aren't a result of trying to add more functionality but rather are a result of personal bias about how something works best. I find that many of the .NET changes fall in this category. For example, what's new about having forms as classes? I've always treated them as such. For implementing inheritance, I found that the Save As choice on the File menu worked nicely. Most of what .NET offers simply causes more work for me with little gain. I don't intend to use Web Services in my programs. And I've been able to achieve almost anything I wanted to do by using a combination of VB 6.0 and the occasional C++ DLL. When I see a true gain for the effort I need to expend to learn the new syntax, I'll do so. But I don't have time to learn something just because it's new.

—Darrell Wilson
[email protected]

Using ADO MD to Retrieve Sets

I enjoyed Russ Whitney's Mastering OLAP column "Ready—Get Sets—Go!" (October 2000) and have a question about one of the paragraphs. The article says, "If you're developing a client application and want to take advantage of server-defined sets (named sets that you can define on the server through Analysis Manager), you need to know about the set schema rowset. ActiveX Data Object, Multi-Dimensional (ADO MD) exposes all the OLAP meta data through schema rowsets. For example, each list of cubes, dimensions, levels, and other information has a schema rowset. Each server-defined set has a schema rowset that includes columns for the set name, set description, expression defining the list of tuples, and the dimensions that the set references." However, I can't find a way to retrieve sets by using ADO MD. Can I retrieve a cube's named sets by using ADO MD? If so, can you give me some more information about how?

—Ipek Guven
[email protected]

You can use ADO MD to retrieve a cube's named sets, but you need the right setup. Here are several things to check:

—Russ Whitney

Apart from the Pack

Bill Vaughn's article "ADO Performance Best Practices" (February 2001) was excellent. I've worked with SQL for about a year now, and two or three of the techniques that Vaughn presented were brand-new to me. I think the proper coding of SQL and the time spent actually learning the many different ways to accomplish a task (and their benefits and drawbacks) are what set experienced SQL developers apart from the pack.

—Mitchell Harper
[email protected]