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!
