How to Work with SQL:2011 Application-Time Period Tables
Consider an example using SQL application-period time tables. Suppose a business wants to keep track of what department its employees belong to at any time throughout their period of employment. The business can do this by creating application-time period tables for employees and departments:
CREATE TABLE employee_atpt( EmpID INTEGER, EmpStart DATE, EmpEnd DATE, EmpDept VARCHAR(30), PERIOD FOR EmpPeriod (EmpStart, EmpEnd) );
The starting datetime (EmpStart in the example) is included in the period, but the ending datetime (EmpEnd in the example) is not. This is known as closed-open semantics.
INSERT INTO employee_atpt VALUES (12345, DATE '2011-01-01', DATE '9999-12-31', 'Sales');
The resulting table has one row, as shown in Table 7-1.
The end date of 9999-12-31 indicates that this employee’s tenure with the company has not ended yet.
Now suppose that on March 15, 2012, employee 12345 is temporarily assigned to the Engineering department until July 15, 2012, returning to the Sales department thereafter. You can accomplish this with the following UPDATE statement:
UPDATE employee_atpt FOR PORTION OF EmpPeriod FROM DATE '2012-03-15' TO DATE '2012-07-15' SET EmpDept = 'Engineering' WHERE EmpID = 12345;
After the update, the table now has three rows.
Assuming employee 12345 is still employed in the Sales department, the table accurately records her department membership from New Year’s Day of 2011 up to the present time.
Deleting data from an application-time period table can be a little more complicated than merely deleting rows from an ordinary, nontemporal table. As an example, suppose that employee 12345 leaves the company on March 15 of 2012and is rehired on July 15 of the same year. Initially, the application-time period table will have one row.
A DELETE statement will update the table to show the period during which employee 12345 was gone:
DELETE employee_atpt FOR PORTION OF EmpPeriod FROM DATE '2012-03-15' TO DATE '2012-07-15' WHERE EmpID = 12345;
The table now reflects the time periods during which employee 12345 was employed by the company and shows the gap during which she was not employed by the company.
An application-time period table of employees may contain multiple rows for a single employee. The employee ID number, by itself, is not usable as the table’s primary key. The temporal data must be added to the mix.
How to designate primary keys in application-time period tables
To guarantee that there is no duplication of rows, the start date (EmpStart) and end date (EmpEnd) must be included in the primary key. However, just including them is not sufficient. Consider the case where employee 12345 was transferred to Engineering for a few months, and then returned to her home department.
Notice that the two time periods overlap. It looks like employee 12345 is a member of both the Sales department and the Engineering department from March 15, 2012 until July 15, 2012.
This adds complication and could lead to data corruption. Enforcing a constraint that says that an employee can be a member of only one department at a time is perhaps what most organizations would want to do. You can add such a constraint to a table with an ALTER TABLE statement such as the following:
ALTER TABLE employee_atpt ADD PRIMARY KEY (EmpID, EmpPeriod WITHOUT OVERLAPS);
There’s a better way to do things than creating a table first and adding its primary key constraint later — instead, you can include the primary key constraint in the original CREATE statement. It might look like the following:
CREATE TABLE employee_atpt EmpID INTEGER NOT NULL, EmpStart DATE NOT NULL, EmpEnd DATE NOT NULL, EmpDept VARCHAR(30), PERIOD FOR EmpPeriod (EmpStart, EmpEnd) PRIMARY KEY (EmpID, EmpPeriod WITHOUT OVERLAPS) );
How to apply referential constraints to application-time period tables
Any database that is meant to maintain more than a simple list of items will probably require multiple tables. If a database has multiple tables, the relationships between the tables must be defined, and referential integrity constraints must be put into place.
You have an employee application-time period table and a department application-time period table. There is a one-to-many relationship between the department table and the employee table, because a department may have multiple employees, but each employee belongs to one and only one department.
This means that you need to put a foreign key into the employee table that references the primary key of the department table. With this in mind, use a more complete CREATE statement, and create a department table:
CREATE TABLE employee_atpt ( EmpID INTEGER NOT NULL, EmpStart DATE NOT NULL, EmpEnd DATE NOT NULL, EmpName VARACHAR (30), EmpDept VARCHAR (30), PERIOD FOR EmpPeriod (EmpStart, EmpEnd) PRIMARY KEY (EmpID, EmpPeriod WITHOUT OVERLAPS) FOREIGN KEY (EmpDept, PERIOD EmpPeriod) REFERENCES dept_atpt (DeptID, PERIOD DeptPeriod) ); CREATE TABLE dept_atpt ( DeptID VARCHAR (30) NOT NULL, Manager VARCHAR (40) NOT NULL, DeptStart DATE NOT NULL, DeptEnd DATE NOT NULL, PERIOD FOR DeptTime (DeptStart, DeptEnd), PRIMARY KEY (DeptID, DeptTime WITHOUT OVERLAPS) );
How to query application-time period tables
Now, detailed information can be retrieved from the database by using SELECT statements that make use of the temporal data.
One thing you might want to do is to list all the people who are currently employed by the organization. Even before SQL:2011, you could do it with a statement similar to the following:
SELECT * FROM employee_atpt WHERE EmpStart <= CURRENT_DATE() AND EmpEnd > CURRENT_DATE();
With the new PERIOD syntax, you can get the same result a little more easily, like this:
SELECT * FROM employee_atpt WHERE EmpPERIOD CONTAINS CURRENT_DATE();
You can also retrieve employees who were employed during a specific period of time, like so:
SELECT * FROM employee_atpt WHERE EmpPeriod OVERLAPS PERIOD (DATE ('2012-01-01'), DATE ('2012-09-16'));