Friday, August 04, 2006

A good application starts with good database design

... and most important - good use of SQL!

That doesn't sound surprising, I guess.

Recently, Giuseppe Maxia provided a good hint (An ugly query should be a warning bell) how to get an indicator that something is wrong with your query. I would like to add a hint to help you find out that something is wrong with the way you use your database inside your application. Just ask yourself the following question:

How much of my client side code is there just to get the data that I got from the database into the form that I need in my application?

Your answer should be: very little.

In most cases, SQL provides the capabilities to get out of the database exactly what you need. Needless to say that it's most important to normalize your data. It's not a bad sign if you end up with complex queries and joins that bind together several tables (just take care that your queries don't look like Giuseppe's negative example ;-)). Place the correct indexes to make sure that these queries still perform well when there are thousands and millions of records in the tables. There are many rules about good database design and they exist for a good reason.

But the message of my words shall be: don't waste hundreds of client side code lines to do what one SQL query can do!

No comments: