The Database Design Process

Introduction

Buck Woody (Blog – @BuckWoody) recently blogged about his Database Design Process and I see Grant Fritchey has a (Blog – @GFritchey) post 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