In PostgreSQL this is achieved with a range type and a special constraint
create table product_price ( price_id serial not null primary key, product_id integer not null references products, price numeric(16,4) not null, valid_during daterange not null -- defines the time span when this price is/was valid ); -- this constraint will ensure that two ranges for one product_id do not overlap alter table product_price add constraint check_price_range exclude using gist (product_id with =, valid_during with &&) -- this works: insert into product_price (product_id, price, valid_during) values (1, 100.0, '[2010-01-01,2011-01-01)'), (1, 90.0, '[2011-01-01,)'); -- but this fails, because the valid date range for the price of 80. -- conflicts with the interval defined for the price of 90.0 insert into product_price (product_id, price, valid_during) values (1, 80.0, '[2012-01-01,2013-01-01)');
In DB2 this is achieved with a special index.
create table product_price ( price_id serial not null primary key, product_id integer not null references products, price numeric(16,4) not null, valid_from date not null, -- defines the time span when this price is/was valid valid_until date not null, PERIOD BUSINESS_TIME (valid_from, valid_until) ); CREATE UNIQUE INDEX check_price_range ON product_price (product_id, BUSINESS_TIME WITHOUT OVERLAPS);