SQL & PL/SQL

Hello everyone,

I believe many people need guidance about this subject. we always see some tables to store system specific parameters and their values but generally VALUES field is just a VARCHAR2 and date/number etc data types are stored in this column. of course this is causing many errors like number decimal point , "15.234" and "15,123" etc. I have a similar situation right now and I want to ask for your opinions to store values like that.

generally structure is like this:

let's assume that this table is used to get system wide general variable's values. those are just dummy variables for sample data.

max_order_limit: the maximum order count per day that company can handle.

e_invoice_product_type: some products doesn't need printed invoice, electronic invoice is enough and those are listed in this parameter table.

new_tax_date: after 01/01/2010, we have to add some new X tax value to the price, not the orders before this date.

etc. as I said these parameters are just fiction. now, this table generally has a structure like this:

create table parameters_Table(

id number(38),

parameter_name varchar2(100),

parameter_value varchar2(4000));

of course storing everything in varchar2 is not something desirable. what do you think that how this should be stored?

how about this:

create table parameters_Table(

id number(38),

parameter_name varchar2(100),

parameter_type varchar2(20),

parameter_value_char varchar2(4000),

parameter_value_number number,

parameter_value_date date

...);

so data in this could be like this based on example above:

this also need some CASE/DECODE statement in the select, because we have to select necessary columns based on PARAMETER_VALUE_TYPE value

select PARAMETER_NAME,

          DECODE(PARAMETER_VALUE_TYPE, 'VARCHAR2', PARAMETER_VALUE_CHAR,

                                                                         'NUMBER', PARAMETER_VALUE_NUMBER,

                                                                         'DATE', PARAMETER_VALUE_DATE) AS PARAMETER_RESULT

FROM PARAMETERS;

/* ps: I am aware that if one parameter has both numeric and char value this sql will fail because one column can have one data type*/

this will cause too many NULL in values columns.

maybe each of these columns should be a table like PARAMETERs_CHAR_VALUE_TABLE, PARAMETERs_NUMBER_VALUE_TABLE etc...

what do you think?

thanks for valuable thoughts.

note: naming standards, data like "VARCHAR2" "NUMBER" etc could be like 1, 2 etc (1 for varchar2, 2 for number...), parameter names could be smaller or bigger, those are not considered in my examples. I just want to show some sample, I interested mainly the structure of table(s)