Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    How to store configuration data?

    Like for example "Current working date", or "Default currency" or "Inventory LIFO/FIFO" - the unique variables, which are used for different purposes (like various configuration options) ?

    The only way I see it is to create a special table , and only using one row with huge amount of attributes... I dont like that to acess those parameters I need to handle this like another table (therefore using complex ADO queries) ,while I really dont need the table like structure, and possible multiple rows in that table will only lead to possible bugs.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: How to store configuration data?

    Originally posted by DarkMadMax
    Like for example "Current working date", or "Default currency" or "Inventory LIFO/FIFO" - the unique variables, which are used for different purposes (like various configuration options) ?

    The only way I see it is to create a special table , and only using one row with huge amount of attributes... I dont like that to acess those parameters I need to handle this like another table (therefore using complex ADO queries) ,while I really dont need the table like structure, and possible multiple rows in that table will only lead to possible bugs.
    It is quite common to have a table called something like SYSTEM_PARAMETERS or SYSTEM_VALUES with a structure like:

    Name Value
    ---- -----
    CURR_WORK_DATE 01-JAN-2003
    DEFAULT_CURRENCY GBP
    ...

    You can hide the table behind a function, so that access is like:

    v_currency := system_value( 'DEFAULT_CURRENCY');

    If you want these values to be accessible from within a client application and without repeated calls to the database server, you could perhaps pre-load them into client variables at the start of a session.

    A benefit of having them in a table rather than in some application file is that they are protected by the DBMS backup and security like any other data.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I would also include the following table, between the two you should be able to store any configutration data needed. I often use the following to manage stateless processes.

    Code:
    CREATE TABLE dbo.SystemParameters (
      RecordID int IDENTITY (1, 1) NOT NULL 
    , Description varchar (35) NOT NULL 
    , DateOne datetime NULL 
    , DateTwo datetime NULL 
    , IntOne int NULL 
    , IntTwo int NULL 
    , CharOne varchar (50)  NULL 
    , CONSTRAINT SystemParameters_PK_00 PRIMARY KEY  NONCLUSTERED (RecordID) WITH  FILLFACTOR = 80
    , CONSTRAINT SystemParameters_UK_00 UNIQUE  CLUSTERED (Description) WITH  FILLFACTOR = 80)
    or with the advent of the sql_variant data type you could have:
    Code:
    CREATE TABLE dbo.SystemParameters (
      RecordID int IDENTITY (1, 1) NOT NULL 
    , Description varchar (35) NOT NULL 
    , ValueOne sql_variant NULL 
    , ValueTwo sql_variant NULL 
    , CONSTRAINT SystemParameters_PK_00 PRIMARY KEY  NONCLUSTERED (RecordID) WITH  FILLFACTOR = 80
    , CONSTRAINT SystemParameters_UK_00 UNIQUE  CLUSTERED (Description) WITH  FILLFACTOR = 80)
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Nov 2002
    Posts
    2
    Great thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •