Chapter 20. Why every SQL developer needs a tools database

 

Denis Gobo

SQL isn’t an object-oriented language. There’s no notion of inheritance. The closest thing that SQL has to objects are views, user-defined functions, and stored procedures. Picture a developer at a software shop; this developer has written a distance calculation algorithm in SQL. Other developers copied this same code for use in their projects. After some time, the original developer finds a small defect in the code he wrote. He updates his code and contacts the other developers so that they can make the modification in their code.

This approach has a few problems; here are three of them:

  • The original developer could forget to contact another developer to make the code change.
  • A lot more people have to make changes now; this will increase the chance of mistakes.
  • All the other developers have to update and test their code to make sure it works as expected.

As you can imagine, it’s much easier to change and test the code in one place. This is the primary reason you need a tools database. Ideally, the tools database should have loosely coupled code and data; it shouldn’t have data and code that depend on another user-created database. To give you an example, the tools database shouldn’t format dates based on a calendar table from the human resources database; the calendar table should be stored in the tools database itself.

What belongs in the tools database?

Creating the tools database

Using an auxiliary table of numbers

Placing common code in the tools database

Formatting

Calling code from a different database

Summary

About the author