SQL Database Design » 12. Auxiliary Tables » 12.3 Constants Tables

12.3 Constants Tables

There is not much to write about constants tables. The approach here is to define a single table which contains only one row containing all the constants, which get their own columns each. Let me cite Joe Celko's SQL for Smarties: Advanced SQL Programming to take over this section:

When you configure a system, you might want to have a way to set and keep constants in the schema. One method for doing this is to have a one-row table that can be set with default values at the start and then updated only by someone with administrative privileges.

CREATE TABLE Constants
(lock CHAR(1) DEFAULT 'X'
      NOT NULL PRIMARY KEY
      CHECK (lock = 'X'),
 pi FLOAT DEFAULT 3.141592653 NOT NULL,
  e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL,
 ...);

To initialize the row, execute this statement:

INSERT INTO Constants VALUES DEFAULTS;

The lock column ensures that there is only one row, and the default values load the initial values. These defaults can include the current user and current timestamp, as well as numeric and character values.

Another version of this idea—one that does not allow for any updates—is a VIEW defined with a table constructor.

CREATE VIEW Constants (pi, e, phi, ...)
AS VALUES (3.141592653), (2.71828182), (1.6180339887), ...;

The next step is to put in a formula for the constants so they can be computed on any platform to which DDL is moved, using the local math library and hardware precision.

Note that I corrected the value for pi from 3.142592653 to 3.141592653.

On MySQL you have to change the type from CHAR(1) to ENUM('X') and probably remove the check constraint, which is ignored by the MySQL parser.

References for Auxiliary Tables:

Last updated: 2010-08-04