Path: utzoo!attcan!uunet!aplcen!uakari.primate.wisc.edu!zaphod.mps.ohio-state.edu!mips!ultra!srini
From: srini@ultra.com (S. Srinivasan)
Newsgroups: comp.databases
Subject: Re: Time related records
Keywords: time,database
Message-ID: <1990Jun28.160558.1974@ultra.com>
Date: 28 Jun 90 16:05:58 GMT
References: <1082@kirk.nmg.bu.oz>
Organization: Ultra Network Technologies
Lines: 64

In <1082@kirk.nmg.bu.oz> cameron@kirk.nmg.bu.oz (Cameron Stevenson) writes:

>We are holding information within a table for a key register. Some of
>the columns are the key number, the person the key is issued to, and
>the issue date. Now supposing that person leaves, the key is handed in
>and issued to a new person. Now the information within the table is
>altered to reflect that change, but the problem... how can I store
>the fact that at some stage person A was issued with that key.

Assumption:	

RELATION Key_Register (	Key#, Person, Issue_Date, Return_Date,
			<other attributes>
		      );


Your hesitation to increase your data storage space by copying tuples
containing Returned_Key information into another historical table,
leads me to believe that your <other_attributes> are significantly
large.  

Why don't you split your relation into:

---------------------------------------------------------------
1. RELATION Key_Issues ( Key#, Person#, Issue_Date, Return_Date);

	INDEX on (Key#);
	INDEX on (Person#);
	UNIQUE INDEX on (Key#, Return_Date);
/* A <NULL> value for Return_Date in the tuple
   which represents a key which has been issued.
   A non-<NULL> value in which the key has been
   returned.  A key which has not been issued out
   to anyone should be present with the Person#
   being the ADMIN.  The UNIQUE index will force you to 
   update the returned-key typle because for a key,
   only one record can be <NULL>
 */

2. RELATION Key_Register ( Key#, <other_attributes> );
	UNIQUE INDEX on (Key#);

3. RELATION Person_Names (Person#, <person_info>);
	UNIQUE INDEX on (Person #);

I suspect this is not normalized enough, because you
could delete all information about Keys from the 
Key_Register table, and leave tuples in Key_Issues
"dangling".  Likewise for Persons.  But your 4GL
should be able to enforce that.
---------------------------------------------------------------

As for your request to store temporal information in general,
doesn't Oracle provide a DATE type of attribute?
If it is internally stored as a 32-bit integer, then
temporal precedence should be simple to establish.

* The Key to the Issue is to have only Date at a Time,
  otherwise you would be in a Unique position indeed!

:-) S. Srinivasan
[Ultra Network Technologies, San Jose, CA]
"The fastest computer networks in the world"
*