Home » Developer & Programmer » Designer » DB Model for auditing and versioning
DB Model for auditing and versioning [message #90618] Tue, 25 May 2004 13:34 Go to next message
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
Junior Member
A question to you E/R modelers out there.

The short question: How do I model for auditing and versioning?

The longer question and example: I have a model I am working on where the data, up to a point, can be done in a straightforward manner by E/R modeling. However, after a certain point, we need to show the changes to any and all fields and log who did the change.

So far, the previous designer did this by duplicating all the records and updating the changed columns. Needless to say, this is expensive in terms of duplicate and redundent data.

I, however, am at a loss on how to implement a version control design using Codd and E/R modeling. I toyed with the idea of an Audit Log table where each record is: TABLE_NAME, COL_NAME, OLD_VALUE, NEW_VALUE but that makes searches for old data impossible. Another idea was to abondon traditional E/R modeling for a modified Star schema - the design used in many data warehouse applications. However the data itself doesn't lend itself to that model readily and I am not as familier with that style of modeling.

How have you solved this problem in your database design? How have you kept with Codds rules of database design and E/R modeling? How can you design a system where, for example, the user wants to know not just what is current but who has changed the data and what values he has changed?

All my DB modeling and design books all seem to be focused on timeless, OLTP designs with a few simple star schema's thrown in for DSS.

Thanks in advance.

--Matthew Iskra

 
Re: DB Model for auditing and versioning [message #90619 is a reply to message #90618] Tue, 25 May 2004 14:56 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Although being a big fan of normaliation, I tend not to be dogmatic about it. Disk is cheap these days and the cost in effort to re-assemble a record from a field level log needs to be taken into account. In my situation this was a good, easy solution. The trigger also has the advantage of de-coupling the history table from the application code.

-- base table to track changes to
create table xyz (
  xyz_id        number primary key,
  xyz_col       varchar2(10),
  DATE_CREATED  DATE                            NOT NULL,
  CREATED_BY    VARCHAR2(30)                    NOT NULL,
  DATE_CHANGED  DATE                            NOT NULL,
  CHANGED_BY    VARCHAR2(30)                    NOT NULL
);

-- ddl to track history on XYZ
CREATE SEQUENCE XYZ_H_S;

CREATE TABLE XYZ_H (
   XYZ_H_ID                        NUMBER              NOT NULL,
   XYZ_ID                          NUMBER              NOT NULL,
   XYZ_COL                         VARCHAR2(10)        ,
   DATE_CREATED                    DATE                NOT NULL,
   CREATED_BY                      VARCHAR2(30)        NOT NULL,
   DATE_CHANGED                    DATE                NOT NULL,
   CHANGED_BY                      VARCHAR2(30)        NOT NULL,
   DELETE_IND                      VARCHAR2(1)         NOT NULL);
--
ALTER TABLE XYZ_H
ADD CONSTRAINT XYZ_H_PK PRIMARY KEY (XYZ_H_ID);
--
CREATE OR REPLACE TRIGGER xyz_h
   AFTER UPDATE OR DELETE ON xyz FOR EACH ROW
BEGIN
   IF DELETING THEN
      INSERT INTO xyz_h
                  (xyz_h_id, xyz_id, xyz_col, date_created, created_by,
                   date_changed, changed_by, delete_ind
                  )
           VALUES (xyz_h_s.NEXTVAL, :OLD.xyz_id, :OLD.xyz_col, :OLD.date_created, :OLD.created_by,
                   :OLD.date_changed, :OLD.changed_by, 'Y'
                  );
   ELSE
      INSERT INTO xyz_h
                  (xyz_h_id, xyz_id, xyz_col, date_created, created_by,
                   date_changed, changed_by, delete_ind
                  )
           VALUES (xyz_h_s.NEXTVAL, :OLD.xyz_id, :OLD.xyz_col, :OLD.date_created, :OLD.created_by,
                   :OLD.date_changed, :OLD.changed_by, 'N'
                  );
   END IF;
END;
/

See this URL too...
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:59412348055
Previous Topic: Very URGENT !!!!!! Oracle Migration Help !!!!!!!!!!
Next Topic: Basic terminology problems!
Goto Forum:
  


Current Time: Fri Sep 25 09:35:59 CDT 2020