Data-Modeling Tools Aren't Physically Fit

In March, I pointed you to a Microsoft survey about a new data-modeling tool that Microsoft hasn't yet announced but is presumably planning. In my commentary "Data Modelers, Arise, and Take Microsoft's New Survey", I asked you what design features are most important for Microsoft to include in a data-modeling tool. I also promised to share my thoughts about what we need in a data-modeling solution.

First, I want to share a dirty little secret about database modeling: Most developers build databases without using a comprehensive logical model. Physical models that specify table design are common, but generally, those physical models are built piecemeal, a few tables at a time. If a logical model exists, the developer typically uses it only early in the project's life cycle. Second, I want to share an opinion that might seem ludicrous to many of my database colleagues. Building databases without logical models isn't the huge problem that most DBAs and data modelers believe it is. I'm not saying that a robust modeling exercise is a bad thing. I simply recognize that life goes on even though many customers skip the modeling phase. Egads! How could such heresy escape my lips? Will I be branded with a scarlet H by a band of angry data modelers? Maybe. But these two points will help you understand what I want in a data-modeling tool. (I assume that you understand basic data-modeling concepts and that I don't have to explain them here.)

Most data-modeling tools ignore the day-to-day modeling and design needs that designers face. Making nice pictures of my tables is a fraction of what I need to worry about and model. I might be stretching the textbook definition of database modeling, but I think a model should include all the physical components of the database system—components that fall outside the scope of traditional data-modeling tools. What about my Data Transformation Services (DTS) packages, SQL Server Agent jobs, stored procedures, indexes, views, indexed views, computed columns, users, roles, permissions, physical disk layout, and anything else I can think of? The list is endless. I want a data-modeling tool to provide traditional database modeling that addresses the physical database and my server-modeling needs.

Failing to model can lead to numerous problems, and I spend a lot of time modeling the systems that I work with. However, I've done performance tuning and other fix-it-after-the-fact problem solving for many customers who haven't conducted a rigorous modeling exercise. Most of these customers' applications are reasonably well designed and meet or exceed the needs of the business community they serve. Plus, few of my clients' performance problems end up being related to schema problems. I think that the developer community has learned basic data modeling. The applications aren't perfect and might not be well normalized, but they aren't crippled by egregious modeling errors either.

Modeling is important, but most people get away with doing less modeling than textbooks recommend. However, many customers suffer severe consequences related to performance, availability, and manageability because they don't have better tools for designing and managing the physical database and server. Maybe it's a pipe dream to think that Microsoft can give me a tool that addresses my logical data-modeling needs and the myriad physical-modeling problems that I need to consider. Maybe—but I can hope, can't I?