There are 2 ways of doing reference tables:
unique hand written tables that perfectly match your desired data
or
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;