What is the best approach (or what options am I not aware of) to creating a static table or view where the data rarely, if ever, changes? An example use-case is a list of US states, where my structure would look something like:
state( id NUMBER(15) PRIMARY KEY, name VARCHAR2(35), abbreviation VARCHAR2(35) )
The two approaches I am currently evaluating are:
Create a table and insert the desired data using a one time data import script
CREATE TABLE state ( id NUMBER(15) PRIMARY KEY, name VARCHAR2(35), abbreviation VARCHAR2(2) ); INSERT INTO state (id, name, abbreviation) VALUES (1, 'California', 'CA'); ...
Concern: data can be manipulated easily and modifying these records would break other parts of the software
Create a view using unions of hard-coded values
CREATE OR REPLACE VIEW state AS SELECT 1 AS id, 'California' AS name, 'CA' AS abbreviation FROM dual UNION SELECT 2 AS id, 'New York' AS name, 'NY' AS abbreviation FROM dual UNION ...
Concern: maintenance, potential for errors when inputting (as opposed to using a sequence and trigger to populate id, for example), and it just doesn't feel right