srini@ultra.com (S. Srinivasan) (06/28/90)
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" *
carl@eleazar.dartmouth.edu (Carl Pedersen) (06/29/90)
My attempt to mail this bounced, so here is my reply to Cameron
Stevenson.
If you have a unique id for the key and for each person, and the
time the key was given to the person, then these three items
together comprise a unique key for the table.
Why not just leave the old records there?
The person who has the key now is the one who took it out most
recently.
select who from t where key_id = 12345 and checked_out =
(select max(checked_out) from t where key_id = 12345);
You may want to keep track of the times when the key is not
checked out to anyone. In that case, if storage isn't a big
issue, you could just have a special "person" who would "hold"
all keys that are not checked out.reg@unify.uucp (Russell Grau) (06/29/90)
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 */
cameron@kirk.nmg.bu.oz (Cameron Stevenson) (06/29/90)
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. Cameron Stephenson Telephone +61 75 951220 Bond University Gold Coast Australia
rbw00@uts.amdahl.com (Richard Wilmot) (06/29/90)
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.
ghm@ccadfa.adfa.oz.au (Geoff Miller) (06/29/90)
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. We had a similar problem with a File Registry System, where we need to identify the current location of a file *quickly* but also record the movement history. We store the location as a multivalued field, and at every movement insert the new location as the first multivalue. An associated MV field record the date of the movement, so we can if necessary strip off movements preceding a given date. Of course, we are working with a Pick-like system (Prime "Information") which makes this kind of thing very easy.... Geoff Miller ghm@cc.adfa.oz.au
rbw00@uts.amdahl.com (Richard Wilmot) (06/29/90)
In Message-ID: <7vi4uht@unify.uucp> reg@unify.uucp (Russell Grau) replies to Cameron Stephenson's request for assistance with a history keeping application with a design suggestion: > 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.... But if the person table includes as a foreign key the primary key (e.g. key#) of the key_register table then a person can only have one key at a time. There are two entity types here: people and keys. There is also a relationship between some of the entity occurrences: issued_to. Another problem is that if I delete a person record (row) then I will have lost some of the history regarding the key that person last possessed. Additionally a person's key possession history cannot be tracked. We could not answer whether John Doe ever had access to a particular if he had been issued a new one so that his key history were overlayed with new key issue information. In short this appears to be a many-to-many relationship: each person can have multiple keys and over time each key may have been issued to multiple people. Many-to-many relationships require relationship tables to store the information about the relationships. In general, choosing to model a relationship as one-to-many is risky compared with electing many-to-many if application/business needs might at some point require it to become many-to-many because application logic assuming the former is hard to change whereas applications developed for the many-to-many case easily handle the one-to-many case. Storing information about the people to key relationships in a saparate relationship table will remove the above update anomalies and tracking limitations. It will then be necessary, of course, to have an integrity constraint to disallow removal of an object which is still involved in a relationship or to cascade such a delete (e.g. maybe we don't care which keys a terminating employee had). Doesn't Oracle support integrity constraints? -- Dick Wilmot (408) 746-6108 | I declaim these as my own ramblings.
cameron@kirk.nmg.bu.oz (Cameron Stevenson) (07/04/90)
From article <0ejm02D3b48801@amdahl.uts.amdahl.com>, by rbw00@uts.amdahl.com (Richard Wilmot): < reply deleted > Thanks very much Richard. The key register is indeed a many-many relationship, and if I read your message correctly you suggested I set up the tables as follows: 1. a person table, with each row representing one person 2. a key table, with each row representing one key 3. a person-key relationship table, with each row representing a relationship between a person and a key - ie. a row for when a person is issued with a key. Sounds fine, and the logic even seems to work - ie. if a person is deleted from the person table (they leave) their entries in the person-key table can be retained for future reference. Let's complicate the issue (believe me, not unnecessarily)... What is really needed from the application are answers to the following questions: 1. Who has/has had access to this room? 2. Which room/rooms can this person get access to? Now we introduce a second set of many-many relationships, as one key can access many rooms, and one room could be accessed by many keys. Does this affect the overall model design, or do I just create a key-room relationship table? The query which traces a person through to the room should be interesting... (perhaps a view would be the way to go?) Just as a final thought to confuse it further... there are always multiple copies of the same key. This means the key number is not a unique instance. Once again, thanks very much to all who replied. Cameron Stephenson Telephone +61 75 951220 Bond University Gold Coast Australia