Re: Variable constants ?

On 16/08/2019 09:27, Rich Shepard wrote:
> On Thu, 15 Aug 2019, stan wrote:
>
>> I need to put a few bossiness constants, such as a labor rate multiplier
>> in an application. I am adverse to hard coding these things. The best 
>> plan
>> i have come up with so far is to store them in a table, which would have
>> only 1 row, and a column for each needed constant.
>>
>> Anyone have a better way to do this?
>>
>> Failing a better way is there some way I can limit this table to only
>> allow one row to exist?
>
> Stan,
>
> I've resolved similar issues with changing regulatory agency staff. 
> For your
> application(s) I suggest a table like this:
>
> create table labor_rate_mult (
>   rate         real primary_key,
>   start_date      date not null,
>   end_date    date
> )
>
> This provides both a history of labor rate multipliers and the ability to
> select either the most current one or a previous one.
>
> If other factors affect the rate, add attribute columns for them.
>
> Regards,
>
> Rich
>
>
I think a better approach is to:

  * include time
  * store independent of timezone (avoids problems with daylight saving)
  * only have one timestamp

    DROP TABLE IF EXISTS labour_rate_mult;


    CREATE TABLE labour_rate_mult
    (
         rate_name         text,
         effective_start   timestamptz,
         rate_value        real,
         valid             boolean,
         PRIMARY KEY (rate_name, effective_start)
    );


    INSERT INTO labour_rate_mult
    (
         rate_name,
         effective_start,
         rate_value,
         valid
    )
    VALUES  -- test data omits time for clarity
         ('junior', '2001-02-01', 4.2, true),
         ('junior', '2008-11-16', 6, true),
         ('junior', '2012-07-23', 4.5, true),
         ('junior', '2019-09-11', 3.7, true),
         ('junior', '2030-12-31', 0, false),
         ('adult', '2001-01-01', 8.4, true),
         ('adult', '2012-07-23', 9.9, true),
         ('adult', '2030-05-03', 0, false)
    /**/;/**/


    SELECT
         rate_value
    FROM
         labour_rate_mult
    WHERE
             rate_name = 'junior'
         AND effective_start <= '2012-07-23' -- stand in for
    CURRENT_TIMESTAMP
         AND valid
    ORDER BY
         effective_start DESC
    LIMIT 1
    /**/;/**/


Cheers.
Gavin

P.S.
Previously, I accidentally just sent it to Rich!