Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!zaphod.mps.ohio-state.edu!mips!pacbell.com!pacbell!sactoh0!unify!reg
From: reg@unify.uucp (Russell Grau)
Newsgroups: comp.databases
Subject: Re: Time related records
Keywords: time,database
Message-ID: <7vi4uht@unify.uucp>
Date: 28 Jun 90 18:48:15 GMT
References: <1082@kirk.nmg.bu.oz>
Reply-To: reg@unify.UUCP (Russell Grau)
Organization: Unify Corporation, Sacramento, CA, USA
Lines: 53

In article <1082@kirk.nmg.bu.oz> cameron@kirk.nmg.bu.oz (Cameron Stevenson) writes:
>This may seem a trivial question for some, but here goes...
>
No question is trivial if you don't know the answer...

>I'm trying to find an elegant way to store time related records with
>our database (ORACLE). By this I don't mean, time in the sense of the
>time lapsed eg. how many minutes, seconds, etc., but in relation to 
>changes in information in columns over time. 
>
>Perhaps an example will explain the problem better...
>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.
>
>I'm sure there are plenty of ways of doing this, but most that I can
>think of involve copying the relevant rows from the 'current' table
>to an 'historical' table, with subsequent increase in the amount of
>data being stored, and fairly strict control on that copy/modify process
>through the application front-end.
>
>So, back to the original question, the answer to which may be staring me
>in the face...
>
>Is there an elegant way to store this time related information? Are there
>any good textual references somebody could point me towards? Any help
>would be appreciated.
>
>Cameron Stephenson                         Telephone  +61 75 951220
>Bond University
>Gold Coast    Australia


Cameron -

I would recommend that you create a "person" table if you do not already 
have one.  This person table would include the name, department, etc., 
etc. that you need for this person.  It would also include as a foreign
key the primary key for your "key register" table, the date that the key
was issued and the date that the key was turned into HQ.

This is the classic one to many relationship with the one being the "key
register" and the many being the "person".

Should be fun....
-- 
/*****************************************************************************/
/*   Russell Grau            (916) 920-9092                 reg@unify.UUCP   */
/* Disclaimer - "I speak for myself, not my company"                         */
/* {{ucdavis,csun,lll-crg}!csusac,pyramid,sequent}!unify!reg                 */