Hi!
Often, we need to store some kind of application parameters, or application user settings.
Naturally, this every parameter has its own type, dimension etc.
We can use a straight table with separate column for each parameter, but if we need to add next parameter, we must alter the table (and prepare get\set method for it).
Another way is to use EAV table (entity attribute value). In this case we need to control types and dimensions by ourselves (which is more dangerous).
But this approach allows us use the general\overloaded getters\setters.
Also, when we prepare setters, its easy to make one setter for many types of data. But getters are a little tricky.
We can't call overloaded functions with different return types.
function get_val(p_param_name in varchar2) return number;
function get_val(p_param_name in varchar2) return date; -- too many declarations ....
Also we can prepare separate method for each data type like
function get_date_val(p_param_name in varchar2) return date;
function get_num_val(p_param_name in varchar2) return number;
Which is the best way to store this kind of data?
Is it possible to prepare general methods without column name mapping using first approach?
Which access method is more flexible and clear?
In my experience every application needs some form of config/settings table. While in theory you should have separate columns to enforce data types, etc., in practice this is overkill. A simple key/value table normally does the job:
create table app_config (
config_parameter varchar2(100) not null primary key,
config_value varchar2(1000) not null
);
As these values rarely change, are typically controlled by the dev team, and the whole app breaks if they're wrong the usual risks around incorrect data types are minimized.
But you do need to take care. The biggest trap I've seen people fall into is using this as a generic lookup table for anything. Avoid this! Create new tables for new entities as needed.
The key mistake I've seen is using one parameter for several values in a comma-separated list. For example:
insert into app_config values ( 'param', 'val1,val2,val3,...' );
This makes the values much harder to manipulate. In this case, you can either:
Create separate parameters for each value:
insert into app_config values ( 'param1', 'val1' );
insert into app_config values ( 'param2', 'val2' );
insert into app_config values ( 'param3', 'val3' );
...
Or have a simple parent-child relationship:
create table app_config_parameters (
config_parameter varchar2(100) not null primary key
);
create table app_config_values (
config_parameter varchar2(100) not null
references app_config_parameters ( config_parameter ),
config_value varchar2(1000) not null,
primary key ( config_parameter, config_value )
);