Sunday, April 26, 2009

Trends in Database Design and Optimized Application Development

I'm constantly going out to customers and looking at their database environments and the challenges they are facing. Of course, sometimes my perspective can be rather unique because customers never invite me in to show me how fast their databases are or how great things are. Usually by the time I get called in, the environment has gotten very dynamic.

What never ceases to amaze me is how on average database design seems to keep getting worse and applications are more poorly written than ever before. There are a number of industry and organization reasons for this but these reasons constantly stand out at customer sites:
  1. Developers Designing Databases: Developers are usually the ones meeting with the customers and doing a lot of the initial design on smaller projects. The problem with this is there is a big difference between understanding what a primary and foreign key are and designing a database. Or there is a data modeler who can design beautiful databases in theory but has no idea how that database design is going to work in a production environment.
  2. Most Developers Do Not Know How to Write Optimized Queries: Back in the old days there were database developers that had a strong understanding of SQL development. However the current generation of Java, PHP, .NET and Ruby developers are great with their languages and writing APIs but the major percentage of them have no idea how to write optimized queries for their applications.
  3. Take Out Databases: In this generation of fast food take out and web applications, its all about getting something out the door quick. Which usually means minimal time for proper database design and testing. This is definitely the Dilbert world of databases.
I find these problems to be much more severe in MySQL environments versus Oracle environments. If an organization pays a million dollars for an Oracle license they are going to make sure they get some people that know that they are doing to design and manage a database project. When someone downloads a MySQL database for free for a small web application they think they can use anybody and the project will turn out okay.

When teaching performance tuning classes in Oracle and MySQL I always ask the students what percentage of companies do they think do a good job of designing their databases and their index optimization paths. I always get single digits for the response to these questions. Oh well, I guess this is what makes the database world so fun. :)

Top things that I have found that greatly improves database application success includes:
  • Getting the right expertise to design your databases.
  • Training developers on how to write good queries for their database applications.
  • Design patterns.
  • Taking the time to do it write.
  • Use case scenarios.

3 comments:

Anonymous said...

I think people do not care about performance until performance becomes a problem. And they probably should not care about performance at all if the application is relatively small. There is a lot more qualities in an application than just the raw speed.

George Trujillo said...

You're right that there are a lot of important qualities in a good application. However, if performance, scalability and architecture are not considered up front they will create problems when an application does grow.

SQL Lion said...

Very Nice post, quiet informative.
Database designing problems being the buzzwords these days, one of the most common designing mistakes is the “Poor Planning / Architecture” of the database or mart. Follow the link to know more…
http://www.sqllion.com/2010/08/database-design-and-modeling-i/