Creating an application-period temporal table results in
a table that manages data based on when its data is valid or in effect.
About this task
When creating an application-period temporal table, include
a BUSINESS_TIME period that indicates when the data in a row is valid.
You can optionally define that overlapping periods of BUSINESS_TIME
are not allowed and that values are unique with respect to any period.
The example in the following section shows the creation of a table
that stores policy information for the customers of an insurance company.
Procedure
To create an application-period temporal table:
- Create a table with a BUSINESS_TIME period. For
example:
CREATE TABLE policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD BUSINESS_TIME (bus_start, bus_end)
);
- Optional: Create a unique index that prevents overlapping
periods of BUSINESS_TIME for the same policy_id. For example:
CREATE UNIQUE INDEX ix_policy
ON policy_info (policy_id, BUSINESS_TIME WITHOUT OVERLAPS);
Results
The
policy_info table stores the insurance
coverage level for a customer. The BUSINESS_TIME period-related columns
(
bus_start and
bus_end) indicate
when an insurance coverage level is valid.
Table 1. Created application-period temporal table (policy_info)policy_id |
coverage |
bus_start |
bus_end |
|
|
|
|
The ix_policy index, with BUSINESS_TIME
WITHOUT OVERLAPS as the final column in the index key column list,
ensures that there are no overlapping time periods for customer insurance
coverage levels.
Example
This section contains more examples of creating application-period
temporal tables.
- Changing an existing table into an application-period temporal
table
- The following example adds time columns and a BUSINESS_TIME period
to an existing table (employees) enabling application-period
temporal table functionality. Adding the BUSINESS_TIME WITHOUT OVERLAPS
clause ensures that an employee is listed only once in any time period.
ALTER TABLE employees ADD COLUMN bus_start DATE NOT NULL;
ALTER TABLE employees ADD COLUMN bus_end DATE NOT NULL;
ALTER TABLE employees ADD PERIOD BUSINESS_TIME(bus_start, bus_end);
ALTER TABLE employees ADD CONSTRAINT uniq
UNIQUE(employee_id, BUSINESS_TIME WITHOUT OVERLAPS);
- Preventing overlapping periods of time
- In the "Procedure" section, an index ensures that there are
no overlapping BUSINESS_TIME periods. In the following alternative
example, a PRIMARY KEY declaration is used when creating the policy_info table,
ensuring that overlapping periods of BUSINESS_TIME are not allowed.
This means that there cannot be two versions of the same policy that
are valid at the same time.
CREATE TABLE policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD BUSINESS_TIME(bus_start, bus_end),
PRIMARY KEY(policy_id, BUSINESS_TIME WITHOUT OVERLAPS)
);
- Ensuring uniqueness for periods of time
- The following example creates a product_availability table
where a company tracks the products it distributes, the suppliers
of those products, and the prices the suppliers charge. Multiple suppliers
can provide the same product at the same time, but a PRIMARY KEY declaration
ensures that a single supplier can only charge one price at any given
point in time.
CREATE TABLE product_availability
(
product_id CHAR(4) NOT NULL,
supplier_id INT NOT NULL,
product_price DECIMAL NOT NULL
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD BUSINESS_TIME(bus_start, bus_end),
PRIMARY KEY(product_id, supplier_id, BUSINESS_TIME WITHOUT OVERLAPS)
);
If the PRIMARY KEY was defined as PRIMARY KEY(product_id, BUSINESS_TIME WITHOUT OVERLAPS)
then no two suppliers could deliver the same product at
the same time.