Unlike traditional application programming, where API functions are changing every day, database programming basically remains the same. The first version of Microsoft Visual Studio .NET was released in February 2002, with a new version released about every two years, not including Service Pack releases. This rapid pace of change forces IT personnel to evaluate their corporation’s applications every couple years, leaving the functionality of their application intact but with a completely different source code in order to stay current with the latest techniques and technology.
The same cannot be said about your database source code. A standard query of ///, written back in the early days of SQL, still works today. Of course, this doesn’t mean there haven’t been any advancements in relational database programming; there were, and they’ve been more logical than technical.
Starting from the days when Bill Inmon and Ralph Kimball published their theories on data warehouse design, the advancements in database programming have been focused on preventing loss of valuable information and extracting all valuable information from the data. Once Inmon and Kimball introduced the database world to data warehousing, major changes were made to ETL (Extract/Transform/Load) tools that gave database developers easy access to metadata, and data from non-relational database sources, which was difficult to work with in the past. This increased the amount of data available from which to extract valuable information, and this increase in available data led to advancements in data processing through OLAP cubes and data mining algorithms.
Adding a data warehouse, OLAP cubes, and data mining algorithms to your database architecture can dramatically streamline business processes and illuminate patterns in your data that you otherwise would have never known existed. Automation can also have a profound impact on business intelligence capabilities.
However, before you start adding new tools and technologies, you should make sure that the transaction database is built properly.
The transaction database is the foundation, and if your transaction database is not reliable and accurate, then adding anything on top is a recipe for disaster.
An important point to keep in mind when adding additional layers to your database is that all projects need to show a return on investment, which is why it’s best to get the most out of your current architecture before adding further layers. All these layers utilize data originating from a transaction database. In many situations you can get the same output by simply querying your transaction database. Of course, having all your reports reading from a data warehouse or OLAP cube is the ideal method, but when an organization is not ready for that level of complexity, it’s more important that its reporting needs are met first. Once basic reporting needs are met, it’s much easier to begin a discussion on how a proper data warehouse, and possibly an OLAP cube, can benefit its business.
Nearly every programmer knows the three rules of database normalization. The stored procedures reading from the transaction database are the path to optimization. The issues to look for are readability, multiple calls to the same database table, and unnecessary usage of variables.
All elite database programmers are picky about the readability of their stored procedures. There are a few commonalities in the way database professionals format their queries, which is different from an application developer. Typically, keywords and aggregate functions are in caps, while table and field names use either camelcase or underscores. Table aliases have some correlation to the actual table name. The alignment of the sections of the stored procedure have some type of block pattern.
Below is an example of a query that utilizes a readable format.
The next thing to look for is if a query hits a table more than once.