Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Relational Database and Versioning

RE: Relational Database and Versioning

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 09 May 2003 11:58:48 -0800
Message-ID: <F001.00595BA7.20030509115848@fatcity.com>


Eric,

Why not just create a history table? Put a trigger on the parent table that captures updates and inserts either a before or after version of the record
(I like the before view, but then the history table lacks the record
creation). The trigger could selectively only insert a record for those columns you wish to track.

This is what I do when this question comes up.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, May 09, 2003 2:43 PM
To: Multiple recipients of list ORACLE-L

In DW, Ralph Kimball is proposing a versionning mechanism known as type 2 : slowly changing dimension. It's well explained in its book : The Datawarehouse Toolkit.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-----Original Message-----
Sent: Friday, May 09, 2003 1:27 PM
To: Multiple recipients of list ORACLE-L

I do exactly this: adding "version_number" column. But, tables where I need "versioning" are relatively small, so I'm not concerned with extra storage.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Gennick
Sent: Friday, May 09, 2003 10:57 AM
To: Multiple recipients of list ORACLE-L

A friend sent me the query below about versioning the data in a database. I thought I'd throw it out to the list and ask what, if any, versioning solutions you see being used.

--
Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com


Thursday, May 8, 2003, 10:42:31 PM,
Eric M. Burke (burke_e_at_yahoo.com) wrote:
Jonathan,

Can you recommend a book that explains different strategies for
implementing data versioning in relational databases? I'm no database
expert. My intuition tells me that for each table, I'd want to start
by adding an extra column containing a revision number. So each
change to the data results in a new row with a new revision.

This seems inefficient, particularly when you have 30 columns and
only two fields changed...it results in a lot of redundantly stored
data.

I guess I'm just not searching for the right thing, because I cannot
find any good info on this topic. I'm mostly interested in "generic"
relational/SQL solutions that would work on a variety of databases,

- Eric


=====
Eric M. Burke
Principal Software Engineer, Object Computing, Inc.
O'Reilly Author
314-494-3185 (mobile)
burke_e_at_yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
  INET: jonathan_at_gennick.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: ineyman_at_perceptron.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: stephane.paquette_at_standardlife.ca Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri May 09 2003 - 14:58:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US