Time and Time Again: Managing Time in Relational Databases Part 16 - Physical Implementation of Version Pattern 1

InfoManagement Direct, December 21, 2007

Starting with this installment in our series, we will begin to describe the physical implementation of these versioning patterns. To this point, patterns have been presented from a logical perspective. Data modelers would recognize these discussions as being about logical data models, rather than physical data models. Almost equivalently, we could say that previous articles in this series have discussed the semantics of versioning rather than its implementation.

We now turn to the corresponding physical data models and to their implementation in a real database. Throughout the rest of this series, we will refer to articles that focus on implementation as "PDM (physical data model) segments" of this series, where each segment is a separate article. We will reference each pattern by version number, and we will use the illustrations and the scenarios of the version pattern within the context of each of these PDM segments.

These PDM segments will show the physical Data Definition Language (DDL) used to create the tables in the schema; the Structured Query Language (SQL) used to populate and update those tables; and the SQL used to query the tables. We will also discuss performance and data integrity in the physical database. In addition, we will describe and discuss known issues, database management system (DBMS) limitations and alternatives to the implementations that are depicted here.

We encourage you to create, populate and query these objects in your own database. By doing so, you will deepen your appreciation of the temporal functionality provided by the early version patterns, and of the more complete functionality provided by the later patterns. In addition, you may learn to develop some of your own variations on these patterns, variations that might better apply in your own DBMS and application environment.

Preliminaries

We will be using Microsoft SQL Server as the DBMS. However, with some minor adjustments, the SQL and the designs suggested in these PDM segments could be used in other DBMSs, such as IBM’s DB2, Oracle or MySQL. Various database objects will be created, including tables, views, indexes, primary keys, foreign keys, triggers and stored procedures. However, we will assume that the reader will create other needed objects, such as databases, containers, file groups and tablespaces, and will have the appropriate permissions to create and use these objects.

In SQL Server the schema owner is often defined as “dbo.” We will not prefix the objects with “dbo,” but that will be the implicit schema name. However, in many DBMSs the default schema name is the same as the current SQL ID or login ID of the person that is connected to the database. This should suffice for most of these examples.

Some DBMSs will implicitly create certain database objects based on the creation of other objects. For example, some DBMSs will automatically create indexes based on the creation of primary keys and foreign keys, but others will not. In many of our examples, we will include the DDL to create objects that might be implicitly created by other DBMSs. Thus, you many encounter some minor syntax or duplicate object errors when declaring these objects to your DBMS. In many cases, you will be able to ignore these errors. We suggest that you confirm that the error does not compromise the model, and that the fundamental purpose of the object, function or statement is fulfilled in some other way.

The SQL we present will often use literals for the variables rather than parameter markers (host variables). This is for illustrative purposes only. Normally, for systems that require high performance, applications should use either statically bound SQL or prepared statements that use parameter markers in dynamic SQL such as JDBC rather than dynamic literals. This strategy saves a significant amount of overhead, avoiding constant reoptimization of the SQL’s access path. So, while we will use literals in our examples, this is not the recommended approach and is only used to clarify the examples.

Note also that some companies have standards to not use a “select” clause. We will use it in our examples simply to save textual space.

Time and Time Again: Managing Time in Relational Databases Part 16 - Figures 1 to 7

Page 1 of 4.