Having a single column to determine the validity of a value has many advantages over a two column (valid_from / valid_to) approach.
- no need to care about gaps
- no need to care about overlaps
- simple way to prevent duplicates possible (unique constraint)
Therefore this is often seen in parameter tables or any other tables storing different versions of an information with a time range validity.
The problem occurs when you try to use the correct information in your queries.
Prepare test case:
Our test case has an application parameter table (key,value,valid_from) holding information about the tax rate of the swiss vat.
CREATE TABLE appl_params (
param_name VARCHAR2(20) NOT NULL
,param_value VARCHAR2(200) NOT NULL
,param_valid_from DATE NOT NULL)
/
A simple primary key on param_name, param_valid_from is sufficient to avoid duplicate validities for one parameter at a point in time.
ALTER TABLE appl_params
ADD CONSTRAINT appl_params_pk
PRIMARY KEY (param_name, param_valid_from)
/
INSERT INTO appl_params VALUES('SWISS VAT','7.6',TO_DATE('01.01.2005','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','8.0',TO_DATE('01.01.2011','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','7.0',TO_DATE('01.01.2014','dd.mm.yyyy'));
COMMIT;
To get the correct tax rate for a sale we need to find the rate belonging to the valid_from which is the latest before the sale takes place…. This is a rather complicated query.
WITH sales (prod, sale_date, price)
AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2015-07-27', 115 FROM dual)
SELECT prod
, sale_date
, price
, TO_NUMBER(ap.param_value)
, price + (price * TO_NUMBER(ap.param_value)/100) AS price_incl_vat
FROM sales s
,appl_params ap
WHERE ap.param_name = 'SWISS VAT'
AND ap.param_valid_from = (SELECT MAX(ap2.param_valid_from)
FROM appl_params ap2
WHERE ap2.param_name = ap.param_name
AND ap2.param_valid_from <= s.sale_date)
/
To support easier and more intuitive queries we create a view which has an additional column param_valid_to. To populate this column we use the analytic function lead.
The solution underneath assumes that param_valid_to as well as sale_date do not include relevant time information (all date columns are truncated to midnight).
CREATE VIEW appl_params_vw
AS
SELECT param_name, param_value, param_valid_from
, LEAD(param_valid_from,1,date '3000-01-01')
OVER (PARTITION BY param_name
ORDER BY param_valid_from ASC) - 1 AS param_valid_to
FROM appl_params
/
After having created this view, the query to find the appropriate tax rate gets quite easy.
WITH sales (prod, sale_date, price)
AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
SELECT 'Product A', date '2015-07-27', 115 FROM dual)
SELECT s.prod
, s.sale_date
, s.price
, TO_NUMBER(v.param_value) vat
, s.price + (s.price * to_number(v.param_value)/100) AS price_incl_vat
FROM sales s
INNER JOIN appl_params_vw v ON ( v.param_name = 'SWISS VAT'
AND s.sale_date BETWEEN v.param_valid_from
AND v.param_valid_to)
/
If the approach is not fast enough we would also have the possibility to materialize this view which is, for a table like the parameter table where changes do not happen to often, not a bad idea.