Creating an application-period temporal table

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.