THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

The Database Design Process

Introduction

Buck Woody (Blog - @BuckWoody) recently blogged about his Database Design Process and I see Grant Fritchey has a (Blog - @GFritcheypost on the same topic. I figured I'd throw my two cents into the mix.

ADD

I often joke that I practice ADD (Andy-Driven Design... what'd you think I meant?). I'm expecting a wikipedia article on this methodology any day now... First, you need someone named Andy on your team. Next, you get Andy to drive the design...

But seriously... I like Buck's and Grant's methodologies and follow something similar when handed requirements. 

I'm handed requirements about half the time these days.

That's Not A Bad Thing

"What?! How can that not be a bad thing Andy?" I use the Scrum methodology every chance I get these days. I find that Scrum works for database design too, provided you design database build scripts that are completely re-executable. Will your scripts build the database from scratch? Will the same scripts only apply changes to bring the database up to the latest version? If not, your scripting technique may not support agile development.

I discuss using SqlCmd to write re-executable T-SQL scripts that provide deployment artifacts in An Example of Test-Driven Development, Part 4 and in a presentation I've been delivering entitled "Database Design for Developers".

Scrum - Not Rugby

Scrum isn't a silver bullet. And it makes for some "interesting" database development.

I encourage database professionals (database developers and DBAs) to join the application and web developer teams early in the development process. Why early? That's when we can make a difference. It will also (brace yourself) give the database professional a glimpse into the topsy-turvy world of web and application development.

I hear you thinking "I used to be a developer Andy."

Me too. And we're not anymore. And things have changed since we were - unless you've left the development game in the last 18 months. "Why 18 months Andy?" Because things change every 1.5 years for application and web devs. "Things change for us too!" Yes, but not that often.

"So, Your Design Process?"

Oh. Yeah, that! :)

  1. Using whatever documentation I have available - which may be written down, or dictated by a developer or customer, or scrawled on a cocktail beer napkin - identify the entities. I iterate a process remarkably similar to that described by Buck Woody (which makes me feel good, but which should worry Buck...).
  2. Write the re-executable Create Database statement:

    If Not Exists(Select name
                  From master.sys.databases
                  Where name = 'MyDatabase')
     begin
      print 'Creating MyDatabase'
      Create Database MyDatabase
      print 'MyDatabase created.'
    Else
     print 'MyDatabase already exists.'
     
  3. Script the tables in T-SQL. Yep, I still type it old school. I like to include my default, check, and key constraints in-line with the row definition. My preference really, I just think they look pretty there.
  4. Deploy! I want a shared sandbox development database out there for the app/web developers to play with as soon as possible.
  5. Respond! Some of the app/web developers are going to change the schema. A subset of them will inform me of this fact. I keep RedGate SQLCompare running and minimized while in a sprint. When the app/web developers check in new code, I compare the shared sandbox development version of the database to my pristine copy. And then I update my scripts to match their version. And then I version-control my scripts along with the app/web code.
  6. As table design stabilizes, I wrap a DPI (Database Programmers Interface) around the CRUD in the form of stored procedures.
  7. Rinse, repeat.

Conclusion

This won't work everywhere and for everyone, but it works well for me. Treat it like a buffet - if you see something that might help, grab that and ignore the rest.

:{> Andy

Published Thursday, January 21, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Buck Woody said:

Very well done - thanks for sharing!

January 21, 2010 10:32 AM
 

Alex K said:

Hi Andy,

Can you please elaborate on: "Will your scripts build the database from scratch? Will the same scripts only apply changes to bring the database up to the latest version?" Why the need for one and the same script? I have only the script which builds the database from scratch. For my deployments, SQL Compare generates the scripts for me without much effort. What in my approach is incompatible with Scrum?

Also I agree on "Script the tables in T-SQL" - IMO it's more efficient this way.

You have not mentioned any unit tests yet. Are you planning to describe that later?

January 21, 2010 11:40 AM
 

Andy Leonard : The Database Design Process said:

January 21, 2010 3:11 PM
 

Peter said:

I actually added some DDL Audits to keep up with what the team changes as far as Schema.  Data changes are a separate issue and harder to capture unless we start adding triggers to e-mail me every time key data changes in a known lookup-type table.  I blogged about my DDL audit method and that may be useful to some others.

February 16, 2010 6:54 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement