Well, I get a number of questions on the relationship between learning and practicing better relational database design principles and writing better, faster or more efficient Sql queries.
In this post, I will answer all these questions or the variations of them that focus on the necessity of learning database design when all you want to do is to write sql queries!
Here are some of the variations of this question:
Do I need to master relational database design in other to write better Sql queries?
Is knowledge of normalization, relational integrity and other database development practices, relevant to writing sql queries?
Why do I need to master entity-relationship concepts or relational database design when all I want to do is write INNER JOINS, OUTER JOINS AND/OR SUBQUERIES?
Here is my answer to these questions asked by aspiring database designers, report writers or sql query writers:
Efficient Sql Queries Depends On Database Index Design
One of the best practices adopted by experienced sql query writers is adding the right types of indexes to their table columns especially those used in JOINS or in WHERE Clauses or other Sql Functions (Group BY, etc.).
Guess what? Those indexes are added as part of the database design, database normalization or sql query performance tuning process.
In other words, learning database design will lead to writing or designing better table indexes which will lead to better sql query performance.
Database Normalization Reduces Data Redundancy & Inconsistency
A lot of time can be wasted trying to get accurate, consistent, reliable reports by writing long, really cumbersome sql queries when there is redundant, inconsistent data in the underlying database.
The real problem is that whenever you have poor normalization or database design, the resulting data redundancy or data inconsistency will make it difficult for a sql query writer to get accurate, consistent, explainable or understandable results!
The reason is that whenever your data is stored in multiple tables because of poor normalization, your sql reports will return inconsistent results because each table contains a different set of data.
This means that your manager or customer will not be able to get an accurate picture of the data because there is no way to verify which of the tables is right or which is wrong!
So, if you want accurate, consistent, reliable sql reports, make sure that you are following the best normalization practices …
Writing Overly Complex Sql Queries Because of Poor Normalization
A heatlhcare services organization hired our company for a database consulting project because their database analysts were getting over-whelmed with providing, accurate, fast, reliable Sql-based reports to medical doctors in their organization!
After our investigation, we found a poorly normalized database with indexes on text columns, manually generated primary keys, multi-valued domains and other bad database design practices.
This healthcare services provider, wasted so much man-hours trying to get consistent, accurate, reliable report for their medical doctors because the results varied depending on who wrote the report or which table was joined to get the information!
This poor database design was also causing their database analysts to write overly complicated, time consuming, error prone or complex sql queries for common, every day tasks!
Since these database analysts were joining patient information tables on text columns, their sql queries were guaranteed to under-perform.
Since the columns where multi-valued, sub-queries had to be used to parse the values of table columns and since the tables did not accurately model the entities used in the business or their relationships, the sql queries where awkward and non-intuitive at best!
The solution was not to spend more time re-writing their sql queries but to normalize and redesign their database, recreate the indexes and then redesign the sql queries from scratch!
If you ever find yourself in a situation where you are bending over backwards in your work, trying to make your sql queries return the results that you want, make sure that your database design models your business process and that it follows best practices!
The job of a Sql Query Writer does not start with just writing efficient sql queries but with learning and practicing database design or normalization best practices.
Only after you ensure that your database design follows generally accepted best practices, are you empowered to write intuitive, high-performing, reliable, consistent or efficient Sql queries or reports.