Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!elroy.jpl.nasa.gov!ames!sun-barr!newstop!sun!amdahl!rbw00 From: rbw00@uts.amdahl.com (Richard Wilmot) Newsgroups: comp.databases Subject: Re: Time related records Keywords: time,database Message-ID: Date: 29 Jun 90 01:59:29 GMT References: <1082@kirk.nmg.bu.oz> Reply-To: rbw00@amdahl.uts.amdahl.com (Richard Wilmot) Organization: Amdahl Corporation, Sunnyvale CA Lines: 73 cameron@kirk.nmg.bu.oz (Cameron Stevenson) writes: > This may seem a trivial question for some, but here goes... > 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. I think you should try some entity-relationship analysis. Offhand, it appears that you are trying to store data that pertains to 2 entities (keys and persons) and 1 relationship: issued_to. The following design might work as a first cut: PERSON_TBL (person_ID, name, phone, etc) --------- person_ID is unique identifier KEY_TBL (key#, key_opens) ---- key# is unique identifier assuming a key only opens 1 thing. Else need a relationship which may be easier to add now. ISSUED_TO (key#, person_ID, issue_date, issue_time, return_date, return_time) ---- --------- ---------- ---------- key#, person_ID, issue_date, and issue_time taken together are unique identifiers in the issued_to relationship table. Now you can have keys that have never been issued (no issued_to rows), keys which have been returned and not reissued (every issue data and time has a nonnull return date and time). You don't need a special history table. I believe that when history and present states are seen as just another (time) dimension then it is much more natural and far easier to write query programs which need to use both current and historical information. This approach will store some identifier data seemingly redundantly in the ISSUED_TO relationship table but only for those objects which have (or have had) multiple relationships. All others will only be ssored once. If you try to compress the issued_to relationship information into one of the other entity's tables you will very likely end up with update anomalies: e.g. can't have a person without a key or a key which hasn't been issued. For more on these and related problems see: "Foreign Keys Decrease Adaptability of Database Designs" Wilmot, R. B., _CACM_, Dec. 1984 Hope this helps. -- Dick Wilmot (408) 746-6108 | I declaim these as my own ramblings.