MANAGING TIME IN
RELATIONAL DATABASES
Companion Web site
Ancillary materials are available online at:
www.elsevierdirect.com/companions/9780123750419
MANAGING TIME IN
RELATIONAL DATABASES
How to Design, Update
and Query Temporal Data
TOM JOHNSTON
RANDALL WEIS
AMSTERDAM • BOSTON • HEIDELBERG • LONDON
NEW YORK • OXFORD • PARIS • SAN DIEGO
SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO
Morgan Kaufmann Publishers is an imprint of Elsevier
Morgan Kaufmann Publishers is an imprint of Elsevier.
30 Corporate Drive, Suite 400, Burlington, MA 01803, USA
This book is printed on acid-free paper.
#
2010 ELSEVIER INC. All rights reserved.
No part of this publication may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or any information storage and
retrieval system, without permission in writing from the publisher. Details on how to seek
permission, further information about the Publisher’s permissions policies and our arrangements
with organizations such as the Copyright Clearance Center and the Copyright Licensing Agency,
can be found at our website: www.elsevier.com/permissions.
This book and the individual contributions contained in it are protected under copyright by the
Publis
her (other than
as may be noted herein).

career in business IT, in such roles as programmer, systems pro-
grammer, analyst, systems designer, data modeler and enterprise
data architect. He has designed and implemented systems in over
a dozen industries, including healthcare, telecommunications,
banking, manufacturing, transportation and retailing. His current
research interests are (i) the management of bi-temporal data
with today’s DBMS technology; (ii) overcoming this newest gener-
ation of information stovepipes—for example, in medical records
and national security databases—by more cleanly separating the
semantics of data from the syntax of its representation; and (iii)
providing additional semantics for the relational model of data
by supplementing its first-order predicate logic statements with
modalities such as time and person.
Randall J. Weis
Randall J Weis, founder and CEO of InBase, Inc., has more
than 24 years of experience in IT, specializing in enterprise data
architecture, including the logical and physical modeling of very
large database (VLDB) systems in the financial, insurance and
health care industries.
He has been implementing systems with stringent temporal
and performance requirements for over 15 years. The bi-temporal
pattern he developed for modeling history, retro activity and
future dating was used for the implementation of IBM’s Insurance
Application Architecture (IAA) model. This pattern allows the
multidimensional temporal view of data as of any given effective
and assertion points in time.
InBase, Inc. has developed software used by many of the
nation’s largest companies, and is known for creating the first
popular mainframe spellchecker, Lingo, early in Randy’s career.
Weis has been a senior consultant at InBase and other companies,

Conventional tables contain data describing what things are
currently like. But to provide comparable access to data describ-
ing what things used to be like, and to what they may be like in
the future, we believe it is necessary to combine data about the
past, the present and the future in the same tables. Tables which
do this, which contain data about what the objects they repre-
sent used to be like and also data about what they may be like
later on, together with data about what those objects are like now,
are versioned tables.
Versioned tables are one of two kinds of uni-temporal tables.
In this book, we will show how the use of versioned tables lowers
the cost and increases the value of temporal data, data that
describes what things used to be like as well as what they are like
now, and sometimes what they will be like as well. Costs, as
we will see, are lowered by simplifying the design, maintenance
and querying of temporal data. Value, as we will see, is increased
by providing faster and more accurate answers to queries that
access temporal data.
Another important thing about data is that, from time to
time, we occasionally get it wrong. We might record the wrong
data about a particular customer’s status, indicating, for example,
that a VIP customer is really a deadbeat. If we do, then as soon
as we find out about the mistake, we will hasten to fix it by
updating the customer’s record with the correct data.
ix