Chapter 2 – The Oracle VALUES table
An article I’ve read time ago gave me the idea to attach an extra table to manage the variable number of columns in different data sets*. This article about dimensional modeling was introducing the concept of “helper table”. In few words, I can create a single column and store a reference (a code, an ID) to another table which holds the real dimensions, no matter how many columns they are. I simply need to create a fake code for the dimension group and link the two tables on that code.
In the “helper table” I may have a row for every dimension, which would be the correct think to do if we want to comply strictly with the normalization rules.
So for the example TSTRAD in Chapt. 1and the helper table would be stored this way:
But I am using Oracle 10g and in this version there is no easy way later to transform several rows into several columns; say for example that I want to select all dimensions in group 1 and display them as columns with the respective value. This would be a very complicated query involving DECODE and MAX and who knows what else. So I am taking advantage of an Oracle feature: the ability to store custom type objects inside table columns, so I create a data type to hold an array of 10 dimensions:
CREATE OR REPLACE TYPE T_VARCHAR2_25 AS VARRAY (10) OF VARCHAR2 (25);
This type will hold up to ten varchar2 strings and since varrays in Oracle are numbered sequentially I will always be sure that they are in correct order. So the “helper table” becomes:
CREATE TABLE UNI_DIM_DIMENSIONS(
DIMENSION_ID NUMBER,
VA_ELEMENT_NAMES T_VARCHAR2_25 NOT NULL, CONSTRAINT PK_UNI_DIM_DIMENSIONS PRIMARY KEY (DIMENSION_ID));

Why do I do this? Can’t I simply store the varray in the original VALUES table and get rid of the helper table? Yes, I can, I do this just in case that two or more values happen to share the same exact dimension columns: in that case I will not need duplicate the information, I will simply reuse the code of the proper dimension group and feel an experienced database developer 🙂
*Kimball, Ralph. “Helper tables let you design and manage multivalued dimensions successfully.” 6 July 1998.