SQL Skills Missing in Developers

Database Design and Support Skills

The biggest assumption is that just because a developer knows SQL that they have to skills to perform database design, data profiling or even data querying. Here are my thoughts on typical skills needed in system development:

Database Modelling Skills

Mission-critical databases should be designed by data architects or at least trained data modellers. Many clients I have worked for used their application developers to model and build their databases. Database design is a skill. The just-build-it approach (just add a table or a column on the fly) is not proper database development – it is a data slum.

Database Design Skills

Too many database are being designed by developers in an ad-hoc approach – missing a table, just add it; missing a column, just add it.  Designing databases is not about creating SQL.  The designer must know what makes a good model and must be knowledgeable and keen on data management best practises.  In short, good database design requires data architecture experience.

Moreover, it is about asking the right questions to come up with a design that will meet the business needs, and be performant and resilient to business change.  One should always use a data modelling tool to design a database and generate the SQL needed to create/modify the database – it should never be hand-crafted.

• Avoid hard-coded SQL statements in application programs that can be used by developers to override expected system logic.
• Design Referential Integrity (RI) into the model so that the database automatically creates the constraints to enforce RI.
• Build all business rules into the tables thereby minimizing and preferably eliminating the hard-coded rules in the applications.

Data Profiling Skills

Just because a developer may know SQL does not mean that s/he knows how to perform data profiling to find all frequent data issues and determine their causes. S/he needs to have keen powers of observation and an unwillingness to ignore data issues.

Data Discovery Skills

Just because a developer may know SQL does not mean that s/he knows how to find patterns in the data and interpret their meaning. S/he may not understand the relationships between the tables.

Query Design Skills

It is not enough for a developer to know SQL to design queries and reports. S/he may not understand the business needs so s/he cannot possibly design the SQL queries needed to produce the outputs.

Database Indexing Knowledge

Just because a developer knows SQL does not mean s/he knows proper SQL construction. The following examples of an actual study showed that many query performance problems were caused by a lack of SQL knowledge, poor design or poor SQL coding.

• Ensure criteria in a WHERE clause are indexed in the database;
• Avoid an equation on left side of a WHERE clause, such as WHERE Amount + X < 5000;
• Avoid WHERE clauses that contain columns inside an equation such as WHERE account_name = bestValue (acc_name, account_name).

These kind of queries cannot use indexing and may result in a full table scan and thus very poor performance.