greengnu@slrpnk.net

greengnu@slrpnk.net

There are 2 ways of doing reference tables:

unique hand written tables that perfectly match your desired data

The RT_ tables pattern mixed with cached views which will give a useful versioned reference table with an effective begin date, meaningful descriptions, version number, the effective end date (If it is set). With the ability to get previous version values if needed, who created the values, when the values were created, who updated the values and when they were updated (And if you follow _A table best practices, all of the previous updates too); not that you would likely need to update the values without doing a version update as well.

Insert in the following order to avoid constraint violations:

RT_TABLE

RT_FIELD_DOMAIN (only need to add entries when creating new reference table views or adding columns to reference tables)

RT_TABLE_FIELD (duplicate old RT_FIELD_DOMAIN values with new table to keep old column names)

RT_FIELD_VALUES (Easiest to do 1 row or column at a time)

Or just insert them all in a single transaction

RT_TABLE design

This is the master reference table for finding what reference tables exist and the versions that exist for them.

| Name            | Null     | Type           |
|-----------------+----------+----------------|
| REF_TABLE_ID    | NOT NULL | NUMBER         |
| TABLE_ID        |          | NUMBER         |
| VERSION         |          | NUMBER         |
| NAME            |          | VARCHAR2(30)   |
| DESCRIPTION     |          | VARCHAR2(255)  |
| COMMENTS        |          | VARCHAR2(255)  |
| STATUS          |          | CHAR(1)        |
| CREATE_USER_ID  | NOT NULL | VARCHAR2(20)   |
| UPDATE_USER_ID  |          | VARCHAR2(20)   |
| CREATE_DT       | NOT NULL | DATE           |
| UPDATE_DT       |          | DATE           |
| UNIQUE_TRANS_ID | NOT NULL | NUMBER         |
| EFF_BEGIN_DT    | NOT NULL | DATE           |
| EFF_END_DT      |          | DATE           |
| ARCHIVE_DT      | NOT NULL | DATE           |

REF_TABLE_ID is the primary key

RT_FIELD_VALUES design

The actual reference table values

| Name               | Null     | Type          |
|--------------------+----------+---------------|
| REF_TABLE_FIELD_ID | NOT NULL | NUMBER        |
| FIELD_ROW_ID       | NOT NULL | NUMBER        |
| FIELD_VALUE        |          | VARCHAR2(255) |
| CREATE_USER_ID     | NOT NULL | VARCHAR2(20)  |
| UPDATE_USER_ID     |          | VARCHAR2(20)  |
| CREATE_DT          | NOT NULL | DATE          |
| UPDATE_DT          |          | DATE          |
| UNIQUE_TRANS_ID    | NOT NULL | NUMBER        |
| ARCHIVE_DT         | NOT NULL | DATE          |

REF_TABLE_FIELD_ID has a foreign key with RT_TABLE_FIELD.REF_TABLE_FIELD_ID FIELD_ROW_ID a sequence value used for all entries on a row

RT_TABLE_FIELD design

This is the glue table for all of the reference tables

| Name               | Null     | Type          |
|--------------------+----------+---------------|
| REF_TABLE_FIELD_ID | NOT NULL | NUMBER        |
| REF_TABLE_ID       | NOT NULL | NUMBER        |
| FIELD_ID           | NOT NULL | NUMBER        |
| CREATE_USER_ID     | NOT NULL | VARCHAR2(20)  |
| UPDATE_USER_ID     |          | VARCHAR2(20)  |
| CREATE_DT          | NOT NULL | DATE          |
| UPDATE_DT          |          | DATE          |
| UNIQUE_TRANS_ID    | NOT NULL | NUMBER        |
| ARCHIVE_DT         | NOT NULL | DATE          |

REF_TABLE_FIELD_ID is the primary key (sequence or uuid) REF_TABLE_ID is a foreign key to RT_TABLE.REF_TABLE_ID FIELD_ID is a foreign key to RT_FIELD_DOMAIN.FIELD_ID

RT_FIELD_DOMAIN design

The actual column names for the reference tables

| Name            | Null     | Type          |
|-----------------+----------+---------------|
| FIELD_ID        | NOT NULL | NUMBER        |
| NAME            |          | VARCHAR2(50)  |
| DATA_TYPE       |          | CHAR(1)       |
| MAX_LENGTH      |          | NUMBER(5)     |
| NULLS_ALLOWED   |          | CHAR(1)       |
| CREATE_USER_ID  | NOT NULL | VARCHAR2(20)  |
| UPDATE_USER_ID  |          | VARCHAR2(20)  |
| CREATE_DT       | NOT NULL | DATE          |
| UPDATE_DT       |          | DATE          |
| UNIQUE_TRANS_ID | NOT NULL | NUMBER        |
| ARCHIVE_DT      | NOT NULL | DATE          |

FIELD_ID is the primary key (sequence or uuid)

RT_ALL_MV design

The master query behind all of the reference tables (keep it cached)

CREATE VIEW IF NOT EXISTS RT_ALL_MV AS
SELECT
   A.NAME AS TABLENAME
  ,A.VERSION AS VERSION
  ,D.FIELD_ID AS FIELDID
  ,A.EFF_BEGIN_DT AS EFFBEGDATE
  ,A.EFF_END_DT AS EFFENDDATE
  ,B.FIELD_ROW_ID AS ROW_ID
  ,D.NAME AS COLUMNNAME
  ,B.FIELD_VALUE AS COLUMNVALUE
FROM
   RT_TABLE A
  ,RT_FIELD_VALUES B
  ,RT_TABLE_FIELD C
  ,RT_FIELD_DOMAIN D
WHERE
  A.REF_TABLE_ID = C.REF_TABLE_ID                AND
  B.REF_TABLE_FIELD_ID = C.REF_TABLE_FIELD_ID    AND
  C.FIELD_ID = D.FIELD_ID;

Example RT_ view

Current values can be just: SELECT * FROM RT_example_MV; For figuring out previous values or making a view:

For sqls that support DECODE

SELECT
   MAX(DECODE(COLUMNNAME, 'CODE', COLUMNVALUE)) AS CODE
  ,MAX(DECODE(COLUMNNAME, 'DESCRIPTION', COLUMNVALUE)) AS DESCRIPTION
  ,MAX(VERSION) AS VERSION
  ,MAX(EFFBEGDATE) AS EFF_BEGIN_DT
  ,MAX(EFFENDDATE) AS EFF_END_DT
FROM FROM RT_ALL_MV
WHERE
  TABLENAME LIKE '%STATUS_IND%' AND VERSION=3
GROUP BY ROW_ID
ORDER BY CODE;

For sqls without

SELECT
   MAX(CASE COLUMNNAME WHEN 'Code' THEN COLUMNVALUE END) AS 'Code'
  ,MAX(CASE COLUMNNAME WHEN 'S0_Rate' THEN COLUMNVALUE END) AS 'S0 Rate'
  ,MAX(CASE COLUMNNAME WHEN 'S1_Rate' THEN COLUMNVALUE END) AS 'S1 Rate'
  ,MAX(VERSION) AS VERSION
  ,MAX(EFFBEGDATE) AS EFF_BEGIN_DT
  ,MAX(EFFENDDATE) AS EFF_END_DT
FROM RT_ALL_MV
WHERE
  TABLENAME LIKE '%example%' AND VERSION=1
GROUP BY ROW_ID
ORDER BY CODE;

Reference table design