5 Replies Latest reply: Jan 10, 2014 9:54 PM by simterico RSS

    application preferences system - best practice

    juliojgs

      Hi,

       

      I'm migrating a forms application from 6i to 11g

       

      The application source forms are the same for all the possible deployments , which are set in different databases (let's say one database for each client).

       

      But as you may expect, each client wants a customized app, so we have to define some kind of preferences, and have them into account in forms (and db packages).

       

      The problem:

      The application, as it was designed, has this customizing system spread over different solutions:

      A database table with one row for each custom parameter.

      A database package constants.

      Forms global variables defined at the main menu.

       

      Even, instead of defininig a good set of properties, I'm finding a lot of code with "if the client is one of this then ... else ..." sentences. Sometimes implemented with instr and global variables defining groups of clients ... bufff....

       

      The question:

      I'd like to take advance of the migration process to fix this a little bit. Can you give advice on a best practice for this?

        • 1. Re: application preferences system - best practice
          Christian Erlinger

          I'd like to take advance of the migration process to fix this a little bit. Can you give advice on a best practice for this?

          What do you mean? Best practice for storing customer-specific parametrization? Even if you have those parameters as constants in a database package the solution wouldn't be very flexible, and code like

           

          if customer_name = 'SOME_CUSTOMER' then
            do_something;
          elsif customer_name = 'SOME_OTHER_CUSTOMER' then
            do_something_else;
          end if;
          

           

          really gives me the creeps. IMHO the most flexible way would be to store those parametervalues in a table; if you give them non-customer specific names (like +do_something_on_something+ instead +weird_customer_x_logic_when_something+) you have an even more general way to customize your application.

           

          cheers

          • 2. Re: application preferences system - best practice
            Andreas Weiden

            I would also go with a parameter-tables in the database.

            • 3. Re: application preferences system - best practice
              juliojgs

              Thanks. I was hoping there would be something better than both approaches (package constants or parameter table) bundled within the database.

               

              Of course the

              if customer name = 'COMPANY_A' then use this logic ...

              gives the creeps to anyone.

              Instead of this everything should be

              if logic_to_do_this = 'A' then ...

               

              There are two minor problems with the table approach:

              Single row and one column for each parameter? (thus you can control the parameter datatype, but need to ddl everytime you add a parameter.

              or a parameter table with parameter name/value pairs? (here you'd have to go with varchar for everything, but you could have a set of pre-established conversion masks)

               

              I prefer the second (you only need to establish masks for number and date parameters), but I'm a bit late, as the app has been working with a single row parameter table from the beginning, and they even didn't wrap it with a getter function.

              In fact, in an old forms application I developed where customization was paramount, I remember I used two tables master / detail: the master table for the parameter "context" definition and the detail table for the values and dates of entry in force (the app worked 24x7 and users even needed to program changes in preferences in advance). A getter function gets you the value currently in force.

              • 4. Re: application preferences system - best practice
                Christian Erlinger

                I'd go for name/value pairs; you could store the datatype along with a numeric/date/varchar2 value; like:

                 

                create table params (
                  param_name varchar2(255),
                  data_type varchar2(50),
                  param_value varchar2(4000)
                );
                
                
                

                 

                and write a functions to get/set values which depending on data_type does a conversion to the correct data type. Or you add a column for each data type, and write a function which depending on the data_type returns the column; like

                 

                create table params (
                  param_name varchar2(255),
                  data_type varchar2(50),
                  char_value varchar2(4000),
                  num_value number,
                  date_value date
                );
                
                
                

                 

                cheers

                 

                Message was edited by: Christian Erlinger On a second thought the second method might be suboptimal as you need a decode everywhere you want to query all parameters. cheers

                • 5. Re: application preferences system - best practice
                  simterico

                  for logging/audit purposes you should always add columns like

                  - mod_date

                  - mod_user

                  plus add some logging e.g. with insert/update triggers