SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Minimalist Design - Part 3

One of the labs I frequently do with students is to design a database for a training business, something which I know something about and can add some challenges as the lab progresses. Students tend to either under or over normalize, though of course that's all in your point of view!

As we work through it I remind them that the cost of development goes up with each join. Yes, SQL Server is good at joins and most of write them without problem, and yes, a normalized database is a good thing. But. As a total guess, I'd say that each join adds at least 10 hours work to a project. Remember that in most cases joins require another form/page/control/screen to support it administratively (you do build admin tools don't you?) and those take time - time to design, code, build, test, revise, deploy, etc.

So let's say you're in the common case of needing to get something to market very quickly that is as good as possible, but time to market is a key factor. As you think about joins affecting design and build time, you can take two approaches - omit building the form/page that allows you to use it/maintain it, or deliberately denormalize the design in key places to help you gain time.

The key...is the key places.

Here's an example. As we arrive at a close to final design I typically see that most classes add a locationid and a corresponding location table - this is to indicate where each class will be held. In my particular case 90% of classes are held in Orlando and location isn't huge, but that could change, and I wouldn't argue it's bad design. The other thing I see that is most often missed is they treat the student as a login and capture demographic data for the student, but fail to capture what is for me key data - the employer at the time they attended the class.

Which of those is easier to fix later? Converting a location varchar(200) to a lookup table, or amending the design to capture employer per class attendee? In the latter case we may have lost data we can't recover, in the former we may have junked up data ('Orlando', 'Orlando, FL', etc). I'd argue that if you had to take a shortcut, do it on location. At worst we have to massage the data to convert it later, and even for reporting now it's not likely to cause huge problems.

To recap, there are two points here. One is that you have to understand a design with 100 tables is going to cost a lot more to build than one with 10 tables, up to you to move the slider. The other is that a really good architect/DBA can help decide which tables to remove when someone sets the slider to something besides 100 so that you can evolve the design with the least amount of pain. There's probably something to be said for creating the full 100 table design, then from that reducing it to the x table design for speed. Think of it as designing your dream house, then building it with 3 bedrooms instead of 6, you'll add more when time and funds permit.


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


Posted by Steve Jones on 29 May 2009

Great point on support tools. They're rarely built, and almost always needed. Course, as you've mentioned before, Access makes a nice support tool.

This is definitely an area we need more case reviews/studies that examine what choices you make in design. And which are more important. I'd agree with you on your example, though a lot of it comes down to a good business understanding from a technical viewpoint. Something few people have.

Leave a Comment

Please register or log in to leave a comment.