Who Owns Database Design?

At TechEd 2010 I had the opportunity to talk with several of the development tools vendors as a part of the Windows IT Pro, DevProConnections, SharePointPro Connections, and SQL Server Magazine Best of TechEd awards program. So many vendors mentioned that database design should be in the hands of developers rather than DBAs that it’s looking like a trend. An ominous trend based on the supposition that DBAs don’t really understand coding and the development process. According to this way of thinking because most DBAs don’t get the idea of what making code objects and business models is all about, therefore they can’t contribute to the development process. Some even went so far as to say DBAs actually hinder the development process by adding unnecessary requirements and other unimportant relational database considerations that aren’t really needed.

 Model-First Database Design—Developers Need a DBA Ally

The key technology fueling this trend is the model-first database design found in tools such as Microsoft’s Entity Framework as well as third-party data access frameworks. These tools allow a developer to create a set of objects that represent the data needed by the application. After the data objects have been designed then the developer can use the tools to automatically generate a database to persist the object values. However, if a developer doesn’t have a strong core understanding of relational databases, the database that’s created might not follow the best relational design principles and it might lack the foreign key relationships, indexes, constraints, rules, and triggers and default values that production-level databases require. In addition, the type of code generated by these data object design tools is often unnecessarily chatty with the database—making too many round trips to the database. Furthermore, the data access calls themselves might be far from optimal if they use inefficient or insecure T-SQL statements.

 Take Advantage of the DBA’s Core Skills

Don’t get me wrong. I’m not saying that model-first design is necessarily a bad thing. A model-first design can definitively help the database developer to create an application with an elegant as well as practical data design. However, relying on the data object modeling tools to build a database without involving the DBA is a guaranteed trip to application disaster. I understand the thrust behind the contention that you don’t need a DBA to design your database, but it’s a position I totally disagree with. Although most DBAs don’t write C# code or create business objects and methods, skillful database design is definitely a core DBA competency. DBAs can lend their training and experience to the design process. Automated tools in particular can’t be counted on to produce good database designs. Poor database design will (not could) result in poorly performing applications. These applications may get past initial testing, but under production loads they won’t scale well and might have concurrency and locking problems. Worse, while you can tweak applications and data access code and even rewrite them with relative ease after an application has been rolled out, changes to the database schema are much more difficult to deploy—especially if multiple databases are in use.

 Frontload a DBA Design Review into the Cycle

The real issue isn’t really model-first design. The real issue is the code-oriented thinking that leads an organization into believing that they don’t need a real DBA to create a production database. For model-first design to be successful, the database design can’t stop with the database generation. It’s a huge mistake to think that the data model in the code is all that’s needed and that the underlying database is unimportant. Generating a persistent database from a model-first design should be the first step not the last step in the creation of the relational database backend. After the developer generates the initial database a DBA or database professional needs to step in to review and modify the database design to enable it to truly handle production workload and data access requirements.

Do you have any thoughts on model-first design and SQL Server? Do you have a passionate interest in database design? I’d like to hear about it at [email protected].