Managing Temporal Data A Five-Part Series
Richard Thomas Snodgrass, Michael H. Böhlen, Renato Busatto, Curtis Dyreson, Heidi Gregersen, Dieter Pfoser, Simonas Šaltenis, Janne Skyt, Giedrius Slivinskas, Kristian Torp, Kwang Woo Nam, Keun Ho Ryu · 1998
Temporal data is pervasive, and challenging to manage in SQL. The June through October issues of Database Programming and Design (volume 11, issues 6–10) included a special series on temporal databases; the five articles in that series are reproduced here. Three separate case studies: a neonatal intensive care unit, a commercial cattle feed yard, and astronomical star catalogs, were used to illustrate how temporal applications can be implemented in SQL. The concepts of valid time versus transaction time and of current, sequenced and nonsequenced integrity constraints, queries, and modifications were emphasized. 1 Of Duplicates and Septuplets This special series explores the many issues that arise when attempting to define and manage time-varying data. Such data is pervasive. It has been estimated that one of every 50 lines of database application code involves a date or time value. Data warehouses are by definition time-varying: Ralph Kimball states that every data warehouse has a time dimension. Often the time-oriented nature of the data is what lends it value. DBAs and application programmers constantly wrestle with the vagaries of such data. They find that overlaying simple concepts, such as duplicate prevention, on time-varying data can be surprisingly subtle and complex. And they are perplexed that trade publications and books do not provide guidance and techniques for handling such data. The five articles in this series will address this need by presenting specific, easily applied ways to manage timevarying data, generally in SQL. Each will include concrete examples of code that can be immediately used in ongoing development efforts. Equally important, we will introduce and illustrate new ways to think about temporal data, imposing structure on a messy topic. In honor of the McCaughey children, the world’s only known set of living septuplets, this first article will consider duplicates, of which septuplets are just a novel special case. Specifically, we examine the ostensibly simple task of preventing duplicate rows, via a constraint in a table definition. Preventing duplicates using SQL is thought to be trivial, and truly is, when the data is not time-varying. But when history is retained, things get much dicier. In fact, over such data several interesting kinds of duplicates can be defined. And, as is so often the case, the most relevant kind is the hardest to prevent, and requires an aggregate or a complex trigger! We’ll first use standard SQL-92, then delve into the machinations required when using DB2, Oracle and Sybase.