Home » SQL & PL/SQL » SQL & PL/SQL » maintain history of all updates for a table and all relational tables
maintain history of all updates for a table and all relational tables [message #432536] Tue, 24 November 2009 19:45 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I want to maintian history of all updates to a table

for example I have a table called Employee the usual columns

now any change I make to this table I have to create a history record first and change the current one , so that any time I can compare differences between the two versions this also includes history for child tables .

I need suggestions on implementing this , what are the best practices for mainitaing history ?


As for my plan

I have two of them

One is to create a history table with exactly the same columns as the actual table

second is to use a discrminator coulmn which tells whether this intance is history or current ?


is any one of is a good design?
or are there any other better ways ?
Re: maintain history of all updates for a table and all relational tables [message #432537 is a reply to message #432536] Tue, 24 November 2009 19:54 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>One is to create a history table with exactly the same columns as the actual table
>second is to use a discrminator coulmn which tells whether this intance is history or current ?
>is any one of is a good design?
no

>or are there any other better ways ?

do you care who made the change?
do you care when the change was made?
Re: maintain history of all updates for a table and all relational tables [message #432538 is a reply to message #432537] Tue, 24 November 2009 20:09 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
no
Re: maintain history of all updates for a table and all relational tables [message #432544 is a reply to message #432537] Tue, 24 November 2009 20:17 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Actually I have to create history when user decides to start an amendment on an employee so not just any change ,but user does bunch of work on employee and other related entities and finishes the job , for any reason changes must be made to employee my application starts amendment process and before I let user work on this employee I have to retain a history of this employee , so before any amendment I want to create history , please suggest me what options have ?

[Updated on: Tue, 24 November 2009 20:22]

Report message to a moderator

Re: maintain history of all updates for a table and all relational tables [message #432545 is a reply to message #432537] Tue, 24 November 2009 20:22 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
do you care who made the change? no
do you care when the change was made? no
Re: maintain history of all updates for a table and all relational tables [message #432546 is a reply to message #432545] Tue, 24 November 2009 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
> I have to retain a history of this employee , so before any amendment I want to create history
OK, for sake of discussion let us say you INSERT current contents into history table, changes are made, & COMMIT is issued.
This occurs hundreds of times over many business days.

One day manager comes to & say the change made yesterday needs to be reversed.

How do identify which row in history table contains yesterday's values?
Re: maintain history of all updates for a table and all relational tables [message #432547 is a reply to message #432546] Tue, 24 November 2009 20:39 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
If I use a discriminator column in my table then I will create a new table which has FK to employee table and version information etc .So this gives me all the versions available , and if it is a history table then updated on

[Updated on: Tue, 24 November 2009 20:40]

Report message to a moderator

Re: maintain history of all updates for a table and all relational tables [message #432550 is a reply to message #432547] Tue, 24 November 2009 21:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
miroconnect@yahoo.com wrote on Tue, 24 November 2009 20:39
If I use a discriminator column in my table then I will create a new table which has FK to employee table and version information etc .So this gives me all the versions available , and if it is a history table then updated on



You mean the history table with timestamp value?
If so how many versions you maintain?
Is this for a single table or multiple tables?

You can impliment this on so many ways....Based on your reply we will give you...

Quote:
OK, for sake of discussion let us say you INSERT current contents into history table, changes are made, & COMMIT is issued.
This occurs hundreds of times over many business days.

read the above one once... again posted by swan.


sriram Smile
Re: maintain history of all updates for a table and all relational tables [message #432567 is a reply to message #432550] Tue, 24 November 2009 23:32 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
hi,
In my view , create a history table exactly same as your current table. craete a trigger on current table. and whenever an update is happened on current table the old values of row go to history table with timestamp( which you can store in one extra column in history table) you may also store the user who actualy made the changes etc by adding additional columns to history table.
Hope this will help

Regards,
Navneet
Re: maintain history of all updates for a table and all relational tables [message #432658 is a reply to message #432550] Wed, 25 November 2009 09:36 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have a set of tables to maintain history not just one table, the parent table and all its children ,
may be 15 tables , My application starts with one table (A) and adds related data to several other tables(B.....Z) ,

mine is a worklfow application , once the main process is finished to make changes I have to start amendment process , before I start amendment process I want to make a copy of the current data for that PK from all tables A...Z and save it.


I need suggestions on whether to create a same set of history tables all the 15 and move date from the actual table to hist before amendment.

or use the same table add a new column which discriminates between the current and revision

so any time I have to start amendment

I update the PK to be amended(1) discriminator column to revision
and create a new row PK(2) with the same data from (1) and DISCRIMINATOR CURRENT


please tell me which one is better




Re: maintain history of all updates for a table and all relational tables [message #432661 is a reply to message #432658] Wed, 25 November 2009 10:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no clear best option.
Option 1 keeps your live tables smaller, and makes querying live data simpler.
Option 2 makes change reporting simpler, and keeps the number of tables in your schema down and makes it easier to abandon a set of amendments and go back to the original version

I'd go with the second option.
Re: maintain history of all updates for a table and all relational tables [message #432879 is a reply to message #432536] Fri, 27 November 2009 00:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
the simple answer to your question is...

Quote:
Use a second table.

I have had best results when I do not mix current rows with historical rows. There area many many reasons why I prefer not to mix them so I won't go into them here. It has a lot to do with philosophy, and with how the data will be used later.

Now the not so simple answer... You are asking to keep history. History is actually a deep and complex problem which is why most database vendors do not do it for you. Indeed, even if you look at Oracle's offerings (FLASHBACK, WORKSPACE MANAGER, TOTAL RECALL), you will see that there are many limitations to each of these strategies. So much so that I do not use any of them. This is a lot for me to say given I think Oracle is the next best thing to sliced bread. But I have tried each mechanism and each one has failed me on multiple occassions in ways that really hurt me. So I have resorted to a DIY approach. But it took me 3 months to figure out how to do a relatively straight forward solution.

There are many details to keeping history correctly, and much of your historical need goes beyond just capturing the data changes correctly. For example, what will you do with the history once you have it? How to you intend to report on it? Can you find a way to reuse your existing programs to generate historical reports, without chaning these programs, or will you have to write brand new reports every time you want a historical view of the data? Lot of work to do there.

You have a lot think about ahead of you. Good luck, Kevin.
Re: maintain history of all updates for a table and all relational tables [message #433078 is a reply to message #432879] Sun, 29 November 2009 15:36 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
can you p-lease tell me what problems I will face if I maintain history in the same table ?
Re: maintain history of all updates for a table and all relational tables [message #433083 is a reply to message #432536] Sun, 29 November 2009 22:20 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
we have also encountered a similar situation.
and we insert data into hist table just before actual update to the main table. Hist and main table have same columns.(Hist table has an extra date column with default sysdate value to know when the row was updated.)
Re: maintain history of all updates for a table and all relational tables [message #433094 is a reply to message #433078] Mon, 30 November 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
miroconnect@yahoo.com wrote on Sun, 29 November 2009 22:36
can you p-lease tell me what problems I will face if I maintain history in the same table ?

- More blocks to scan when accessing current data
- Access to history and current data are not of the same type -> it is better to not mix the workload and it is likely storing them in appropriate devices for the kind of accesses.

Regards
Michel

Re: maintain history of all updates for a table and all relational tables [message #433177 is a reply to message #433094] Mon, 30 November 2009 09:24 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
To create same set of tables as history tables do you suggest have all tables and add a suffix HIST ?,

All my tables one parent and several children tables right now its 15 tables and it will grow further , do you recommend creating all tables for history ?
Re: maintain history of all updates for a table and all relational tables [message #433185 is a reply to message #433177] Mon, 30 November 2009 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: maintain history of all updates for a table and all relational tables [message #433189 is a reply to message #432536] Mon, 30 November 2009 09:56 Go to previous messageGo to next message
rmurali4u
Messages: 2
Registered: November 2009
Location: US
Junior Member

Hi,

Apart from all the columns of employee table, it is best practice to have auditing columns like UPDATE_DATE/UPDATE_USER_ID,INSERT_DATE/INSERT_ID.

Are you using trigger to capture the update event?

Thanks and Regards
Murali
Re: maintain history of all updates for a table and all relational tables [message #433191 is a reply to message #433177] Mon, 30 November 2009 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
how do you audit DELETE operations?
Re: maintain history of all updates for a table and all relational tables [message #433206 is a reply to message #432536] Mon, 30 November 2009 11:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You will need some suffix for you history tables (also called a shadow table in some circles (see Tom Kyte at ASKTOMHOME)). I use _H when I do history.

Additionaly your question now gets into what I was talking about with regards to "THERE IS SO MUCH MORE TO THINK ABOUT WHEN IMPLEMENTING A HISTORY SOLUTION". For example, suppose you have a report based on this view:

create or replace view v_salary_emp as
select emp_name,dept_name,salary
from emp,dept
where emp.dept_id = dept.dept_id;

select * from v_salary_emp where dept = 10;

The above query will use the view to show you employees in deptartment ten currently.

Suppose next that you want to create a historical report to answer the question for prior points in time. You have two choices:

1) do you create a new report

2) do you reuse the same view and query above, just faking oracle out by making it provide last months data (or whatever prior point in time you want).

Most people think the only way to do it is by #1. #1 certainly works but it is not the only solution and to my mind not the best one because it forces you to write new code every time you want to look at data from a historical perspective. And it is error prone. What if your DBA one day decides to change the meaning of EMP and replace it with its own complex view that contains existential subqueries and function calls. Your historical reports now have the potential be be wrong because you will not have access to the underlying code because your DBA hid the code for these things.

Indeed, this is one of the foundations of good database designs in my opinion, designs that exploit encapsulation properly. But what a waste to have to write all new code. Too bad we don't have a way to reuse those thousands of apps with 1/2 million lines of code across our Oracle databases that everyone already knows how to use. OH... HANG ON... WE CAN.

This then is the real power of a good historical design. The abilty to reuse existing code, and removal of restrictions on what kind of code people can write. But this requries a plan and a design strategy and most people concentrate on how to save the historical data in tables, not how to get it out.

Consider this series of sql events

--
-- your original tables
--
create table emp ...

create table dept ...

--
-- your shadow tables that store history of the data
--
create table emp_h ...

create table dept_h ...

--
-- your original salary view that shows current data
--
create view v_salary_emp as ...

--
-- historical view which gets a date using sys_context()
-- this view is the first step in a history retrieval strategy
-- this view would return data for emp based on the date it gets
-- essentially it rolls back your database to a point in time
--
-- note we create one such view for each table
--
create or replace view v_emp_historical as
select *
from emp_h
where [Bob Barker lookup goes here using sys_context()];

create or replace view v_dept_historical as ...

--
-- create a historical user for viewing history
-- this is the second step in a historical retrieval strategy
-- creating a way for people to indicate they are doing a historical query
-- my preference is with a new user for this specific purpose
--
create user historical_user...

--
-- log into the historical user
--

--
-- create objects for each rowsource that point to your historical views and not the tables
-- notice how we are using the synonym to not only redirect to our historical view
-- but also to rename that view to the original table name
--
create synonym historical_user.emp for data_owner.v_emp_historical;

create synonym historical_user.dept for data_owner.v_dept_historical;

--
-- here is your original salary view
-- but now it points to historical views under the covers
-- notice we can compile this view in our historical user without changing it
--
create or replace view v_salary_emp as
select emp_name,dept_name,salary
from emp,dept
where emp.dept_id = dept.dept_id;

--
-- set a date for your history point in time
--
exec my_package.set_historical_date([somedate])

--
-- execute the same code you did before
-- but now it sees the historical point in time
-- there you go...
-- exactly the same code that got current data can get historical data
--
select * from v_salary_emp where dept = 10;

The point of the above is that you were able to reuse all your existing code without changing it, just by implementing a historical design strategy in your system. That is way better than writing historical queries anew. Additionally the answers you get will be correct no matter what your DBA does to the original stuff under the covers in the future because all the objects they work with will follow the same rules for history and thus will offer up historical versions of themselves and thus complex code will in historical mode retrun the correct result.

By letting your DBA make history a database feature, your Developers are not burdened with the details of history capture or history retrieval.

As you see, history goes beyond just saving the data. You need to figure out how you will get answers out of the saved data. You have to decide if you want to jump into something quick and cause future issues, or if you want to take the time to really understand history solutions and do something that will stand the test of time.

This is one reason most people don't have systems that do history or systems with history that has hidden errors; because they have no stomach to take the time and figure it out.

Good luck, Kevin
Re: maintain history of all updates for a table and all relational tables [message #433207 is a reply to message #432536] Mon, 30 November 2009 12:01 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
For BlackSwan

I have handled auditing deletes in two differnt ways.

1) use a flag on the end of the last version of the row you saved and set it when you delete the row

2) store another version the row with the delete date and a flag that says this is a delete.

My experience is #2 is best by far. There is a lot to be said for each insert/update/delete on a row having a corresponding entry in your shadow table.

This question though (how to handle deletes) is a great introduction to the complexity of history. If forces you to answer so many questions about how your historical tables will work.

Kevin
Previous Topic: Data Insertion Problem
Next Topic: Difference between PRIMARY KEYS and SURROGATE keys
Goto Forum:
  


Current Time: Mon Sep 26 02:34:11 CDT 2016

Total time taken to generate the page: 0.11270 seconds