8 - Auxiliary Tables

Abstract

This chapter focuses on auxiliary tables that are a way of building functions and look-up tables that would be difficult if not impossible to do with the limited computational power of SQL. Auxiliary tables serve as adjuncts to do queries via joins rather than computations and they are usually very static and constructed from an outside data source. Thus they do not require the same constraint for safety and they need to have a primary key declared so that it creates a fast access method for searching and joining the auxiliary table to other tables in the schema. The most important auxiliary table is a Calendar as the Common Era calendar is too irregular for easy computations. The Series table is a simple list of integers from 1 to (n) that is used in place of looping constructs in a procedural language. The most common form of look-up tables has two columns, one for the value to be looked up and one for the translation of that value into something the user needs. Although most auxiliary look-up tables are meant for simple decoding, they can also be used for more complex functions such as programming the Net Present Value (NPV) and its related Internal Rate of Return (IRR) in SQL.