Editorials

Because “It Depends”







As software professionals, one of our primary strategic goals is to eliminate redundant work. If we manage servers, we want to automate that management as much as possible. If we write software, we want code re-use. If we are writing boiler plate code where we are simply applying the same pattern to a new object, we want to automate that code generation.

We are getting pretty good at automating, or having computer systems automate the redundancy in our work. A great example would be ORM generators. They work great! Well, actually, they can work great. Or, sometimes, they can be our worst nightmares.

Here is the central theme of today’s topic. In just about any IT discipline you can ask what is the best way to do something, and many times you will have an answer begin with, “It depends.” You can use all of the boiler plate code and system generators you want, but, to date, most of them don’t handle “It Depends”. They work great for the “Happy Path” against which they have been created. But, get into new circumstances, and things begin to fall apart. Sometimes they fall apart in a big way.

Yesterday we talked about a database having more than 300 materialized views. In the comments to the editorial I read people responding that they had heard that views were a good thing. They are correct, except, “It Depends”. You can read the editorial for yesterday if you want more technical details about views. I using that as an example of why we still need database professionals today.

I can’t tell you how many times I’ve heard, “We don’t need Database Designers anymore because we can generate the database from the application code. And it auto-migrates. And it keeps the code and the database synchronized.” And then, because they haven’t learned the database areas where the implementation “Depends”, things fall apart.

The inverse is just as true. How many times have we as database designers made the job of application developers more complicated, simply because we don’t understand the app code scenarios where “It Depends”.

At least for the near future we are going to need people who understand when the best answer is “It Depends”. As long as that domain of knowledge is big enough, we are going to need specialists, or at least people with deep understanding. We are going to need database professionals, not just web developers or application developers. So, don’t throw away your SQL Engine hat for a while folks. If you want to expand, pick up another Engine, or another software layer…but I think we are going to need you now more than ever.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Eilenblogger

    Ben

    I am absolutely sure that I’m in the minority with my perspectives on Views and Entity Framework.

    I think there should be an abstraction layer between the database and the application. That abstraction layer should be Stored Procedures.

    No Views, no Code First. That doesn’t mean no Entity Framework but it does mean simplifying the application layer from all the bizarre LINQ implementations many of which are repeated (oh so minutely differently) for other uses (inconsistent redundancy).

    If a developer consumes a stored procedure and the underlying schema has changed to support some other functionality then the stored procedure can change to accommodate the schema modifications thereby insulating the application layer.

    If the schema changes, and you have gobs of views then you need some mechanism to define which gobs have been impacted. Then you need to find all the places in the application that touch the gobs of views.

    I think the objective of code first entity framework was to “easily” synchronize the application with the database, but good grief…

    Views facilitate ignorance whether they are materialized or not. Write a stored procedure that joins only to the tables and columns you need and forget about the perceived benefit of a view. There are no benefits to a view, they facilitate ignorance of the database schema and oftentimes they join to tables that you don’t even need for your use-case. Of course you could create yet another view that only joins to the tables and columns you need but then you have another view and the utility of that view might be specific to one piece of code in the application, hey but maybe two pieces of code in the application (congratulations you just eliminated redundancy).

    • David Eaton

      Well I couldn’t agree more! For the last dozen years as a database consultant, All my work has been to come in and redesign the databases built developers using ORM tools. It has made me quite a great deal of money.

      The problem is that the ORM tools have no clue about actual database design. They just build tables to emulate screens. There is no structure for expansion. Funny thing is, I dealt with a materialized view yesterday. It add 165 lines in the select statement, joins 11 tables on the column’s values not keys. The result set had over 11 million rows and takes over 6 minutes to respond. Clearly the idea of data growth never crossed the developers mind. Or the ORM’s mind was somewhere off in code.

      Time and time again, there is no time to design the database using proper database design principles. This leads to horrible performance.

      Again, things like analyzing possible data growth, file groups, and analyzing indexes, not realizing that sometimes data needs to be rolled up and correlated for reports and data marts and forensic investigation never enter the purview of the developer, and these are the reasons that applications work and then fall apart so quickly.

    • Martin van Gils

      You may be surprised. However, “No Views”? That’s quite a statement. As my old Professor Gerard Rijsenburg used to say: “You can’t make pigs out of meat loaf”. What he meant was that in (Entity-) Relational systems, having the most top logical level available as a tangeable object makes discussions with end-users much more easy. Technical implementation is not leading, but following. Let’s say we have three tables, that can be joined in an updateable view. Any C, C# programmer would be able to program against such an object, blessedly unaware of the underlying physical model. So, more hands available, and the database designer can focus on other tasks that suit him/her better. How cool is that?

    • Ben

      I tend to be more open that that. However, even when using Entity Framework, I prefer to use stored procedures. What this means is that you are getting the transformation from sets to objects through EF, but still have the obfuscation and power of the stored procedure.It saves some time, and provides a nice integration with the Repository Pattern.

      From personal experience, I know as David writes below, that systems are often bigger than the customer really understands. I write a system for a department. It doesn’t need optimized ORM because it is only for this department. Then, the next thing you know they have 50 users, and the ORM can’t keep up.

      At the end of the day, you have to expose some aspect of your database to the consumer. Using the Stored Procedures allows you to expose only what you need, and change things behind the scenes. In essence, the SP becomes an interface to the application. The app doesn’t know how it works, only that it does. There’s a lot to be said for that.

      • Eilenblogger

        I agree.

        Also I didn’t mean that you shodnt use EF, but just that should have another layer using sprocs.